Re: New to SQL server

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


dbahooker@xxxxxxxxxxx wrote:

DRI and CHECK constraints?

SQL SERVER WORKS LIKE A CHARM, PLEASE EXPLAIN YOUR SO CALLED BUGS

I didn't call them bugs; rather, they are omissions by design.

Both Cascade referential actions in DRI and table-level CHECK
constraints are both features of _full_ SQL-92 and Microsoft only
claims _entry level_ SQL-92 compliance for SQL Server (and not even
that for Access/Jet). Still, I think it's a poor show that these
features have been in Access/Jet for so long yet still missing from SQL
Server.

Didn't I already provide examples? Oh yeah, you didn't like
self-referencing FKs and inline constraints. OK, try this:

CREATE TABLE Employees (
employee_ID INTEGER NOT NULL
)
;
ALTER TABLE Employees ADD
CONSTRAINT pk__Employees
PRIMARY KEY (employee_ID)
;
CREATE TABLE OrgChart
employee_ID INTEGER NOT NULL,
manager_employee_ID INTEGER NOT NULL
)
;
ALTER TABLE OrgChart ADD
CONSTRAINT pk__OrgChart
PRIMARY KEY (employee_ID)
;
ALTER TABLE OrgChart ADD
CONSTRAINT fk__OrgChart__employee_ID
FOREIGN KEY (employee_ID)
REFERENCES Employees (employee_ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
;
ALTER TABLE OrgChart ADD
CONSTRAINT fk__OrgChart__manager_employee_ID
FOREIGN KEY (manager_employee_ID)
REFERENCES Employees (employee_ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
;

The above is SQL-92 code so it can used in Jet (e.g. ADO or, if you
prefer the GUI, in 'ANSI-92 Query Mode').

It works fine in Jet. For SQL Server the last statement generates and
error, 'Introducing FOREIGN KEY constraint
'fk__OrgChart__manager_employee_ID' on table 'OrgChart' may cause
cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'

You see, Jet has been programmed to resolve most cascade paths whereas
SQL Server is programmed to count the number of paths and choke when it
gets to 2; the fact the paths can be resolved easily is of no
consequence because SQL Server simply does not have the ability to do
it. I figure they were too busy trying to get CLR into the product
rather than get TSQL up to standard ;-)

Of course, you could write a trigger to perform the cascade action for
the second one but you have to resolve the paths yourself, do error
handling, transaction management, etc. Don't you think it a bit of a
weird table design that one cascade would be done with a FK and other
done with a trigger? Or would you write them both as triggers? All your
FKs as triggers? And you thought it was only Access/Jet where you had
to write your own tools to do basic operations <vbg>!

As for CHECK constraints:

ALTER TABLE OrgChart ADD
CONSTRAINT manager_for_maximum_three_employees
CHECK (NOT EXISTS (
SELECT T2.manager_employee_ID, COUNT(*)
FROM OrgChart AS T2
GROUP BY T2.manager_employee_ID
HAVING COUNT(*) > 3))
;

Works fine in Jet but in SQL Server it generates an error, 'Subqueries
are not allowed in this context. Only scalar expressions are allowed.'
Table-level CHECK constraints must be implemented in SQL Server with a
trigger. Don't get me wrong: it's great that SQL Server has a trigger
language to be able to plug these gaps; my point is, DRI and CHECK
constraints are the basic fundamentals and I have better things to do
with my time that to code around Microsoft's omissions.

Jamie.

--

.

Relevant Pages

  • Re: Pb with on delete cascade... HELP!!!
    ... Cascading foreign keys for update and delete were introduced in SQL Server ... > I really don't understand what is wrong with my database... ... use this "DELETE ON CASCADE" command:o( ...
    (microsoft.public.sqlserver.programming)
  • Re: New to SQL server
    ... and cascading updates and deletes. ... I think DRI is better implemented in Jet 4.0 than SQL Server. ... ON UPDATE CASCADE); ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Cascading delete on self referenced table
    ... Then create a trigger to do the ... Wayne Snyder, MCDBA, SQL Server MVP ... > 'Test' may cause cycles or multiple cascade paths. ... > ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. ...
    (microsoft.public.sqlserver.programming)
  • Re: Pb with on delete cascade... HELP!!!
    ... Make sure that u used ON DELETE CASCADE ... > The parameter of the compatibility level of my database is 70, so it seems to be OK according to the documentation of SQL server 2000 to use the key word "CASCADE". ... > "Ninizzzzz" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: New to SQL server
    ... ON UPDATE CASCADE); ... wouldn't work in SQL Server since I don't have a table named OrgChart ... Eat a dick lamer; Jet is a DISEASE. ... I think DRI is better implemented in Jet 4.0 than SQL Server. ...
    (microsoft.public.access.adp.sqlserver)