Translating referential actions
-
Hi, again, I have been struggling to figure out how to translate Celko's SQL-92 standard code into something SQL Server 7 will accept to no avail. I managed to make a small amount of headway but the code entered below stumps me.
[I'm showing the whole create table code just to give you a context.]
create table Orders
(orderid integer not null primary key,
empid integer not null default 0,
custid integer not null,
salesdate date not null default getdate(),
foreign key(custid) references Salespersons(empid)
on update cascade
on delete set default,
foreign key(custid) references Customers(custid)
on update cascade
on delete cascade);
returns the error
Server: Msg 156, Level 15, State 1, Line 7
Incorrect sytax near the keyword 'on'.
which is probably obvious to those in the know. I managed to get past line 6 by putting parenthesis around 'custid' after 'foreign key' but for all I know I made it worse. I tried looking up foreign key, referential actions, on and update in BOL but wasn't able to match the sytax. It's either because I just didn't get it or this type of referential action isn't available and requires some other solution. Beats me!
Sivea
Sivea -
One of the things that was expected in SQL Server 7 but didn't materialize is cascading referential integrity. It is present in SQL Server 2000.
In SQL Server 7 the only way to have cascading "integrity" is by the use of triggers (and with that, you couldn't use any referential integrity constraints, because the constraints are checked before the triggers fire). So therein lies the cause of the errors you are receiving.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Thanks again, Brian. I will simply leave out the offending cascades until I get to SQL Server 2k. For now, it's enough to know about both the shortcomings of SQL Server 7 and the possibility of cascading "integrity". Bummer about the triggers firing before the constraints could be checked, though.
Sivea
Sivea -
The triggers/constraints issue in SQL Server 7 meant that I had to manually delete from the bottom up with respect to a SQL 7 database I designed. It wasn't terribly difficult, because it was a fairly simple design, but during that whole project I remember saying to myself, "Access has cascades... why in the world didn't these guys put it in SQL 7?!?"
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
I personally disklike the cascading deletes. Maybe I'm cautious, but I prefer that the rows be explicitly deleted.
Steve Jones
-
I agree cascading deletes are powerful and dangerous, but what about cascading updates? That's pretty darn useful for maintaining data integrity if properly constrained. I am looking forward to the possibilities in SQL Server 2000.
Sivea
Sivea -
Mixed feelings. I hate having something scripted that affects more than one table or update. It prevents you from handling changes to your application that you might not forseen. Maybe because I've been with a few startups where the data modeling is done on the fly becuase the requirements I have today didn't exist 6 months ago.
Steve Jones
-
Mixed feelings. I hate having something scripted that affects more than one table or update. It prevents you from handling changes to your application that you might not forseen. Maybe because I've been with a few startups where the data modeling is done on the fly becuase the requirements I have today didn't exist 6 months ago.
Steve Jones
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply