Sql Server on delete cascade !! 1

[shuklix]

It seems like I am having a bad day! can any please please point at the mistake in the following peice of code.

CREATE TABLE Sale (
saleid integer NOT NULL,
clientid integer NOT NULL,
productid integer NOT NULL,
saledate datetime NOT NULL,
CONSTRAINT pk_sale_id primary key (saleid),
CONSTRAINT fk_client__clientid_sale FOREIGN KEY (clientid)
REFERENCES Client(clientid) ON DELETE CASCADE,
CONSTRAINT fk_product_pid_sale FOREIGN KEY (productid)
REFERENCES Product(pid)
);

I get the following error

Server: Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'ON'.

TIA,
Saurabh

 

[JamesLean]

Which version of SQL are you using? Cascading referential integrity is only supported on SQL 2000.

--James

 

[shuklix]

Thanks James!! I am using MS Sql Server 7.
Now the question is, how do I go about implementing it
in Sql Server 7, should I create triggers ?

thanks,
Saurabh

 

[ClaireHCM]

You can only install sql 2000 to do this task.
Otherwise,you need to drop all the foreign key in order to drop the primary key

 

[shuklix]

I can not have Sql Server 2000, I have to use sql server 7.

I would not like to drop the constraints. I can always handle it when I am writing my SP's. I just thought there may be an elegant and better way of doing it.


Saurabh

 

[JamesLean]

Yes, the easiest way is to use triggers to enforce this integrity in earlier versions of SQL.

Or, as you say, write SPs to manually do it.

--James