Change Delete Rule - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Change Delete Rule

Author

Topic

rn5a
Starting Member

25 Posts

Posted - 2008-12-16 : 02:08:09

I have 2 tables - TableA & TableB. TableA has 2 columns - TID & TName where TID is the primary key. This is how I created TableB:

CREATE TABLE TableB(
SID varchar(5),
SName varchar(20),
CT varchar(5),
FOREIGN KEY (CT) REFERENCES TableA (TID) ON DELETE CASCADE
)

Now after creating TableB, I want to change the DELETE rule from CASCADE to SET NULL. What should be the ALTER TABLE statement to incorporate this change?

Thanks,

Ron

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 02:10:40

just use ALTER TABLE TableB DROP CONSTRAINT... to drop it and then
ALTER TABLE TableB ADD CONSTRAINT
to recreate it with new option

rn5a
Starting Member

25 Posts

Posted - 2008-12-16 : 02:34:09

quote:
Originally posted by visakh16

just use ALTER TABLE TableB DROP CONSTRAINT... to drop it and then
ALTER TABLE TableB ADD CONSTRAINT
to recreate it with new option

OK...I dropped the constraint & then added a new constraint in this way:

ALTER TABLE TableB ADD CONSTRAINT fk FOREIGN KEY (CT) REFERENCES TableA(TID) ON DELETE RESTRICT

but it generates the Incorrect syntax near the keyword 'RESTRICT' error. What am I doing wrong? If I use

ALTER TABLE TableB ADD CONSTRAINT fk FOREIGN KEY (CT) REFERENCES TableA(TID) ON DELETE SET NULL

then it works fine.

Thanks,

Ron

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 02:42:36

quote:
Originally posted by rn5a

quote:
Originally posted by visakh16

just use ALTER TABLE TableB DROP CONSTRAINT... to drop it and then
ALTER TABLE TableB ADD CONSTRAINT
to recreate it with new option

OK...I dropped the constraint & then added a new constraint in this way:

ALTER TABLE TableB ADD CONSTRAINT fk FOREIGN KEY (CT) REFERENCES TableA(TID) ON DELETE RESTRICT

but it generates the Incorrect syntax near the keyword 'RESTRICT' error. What am I doing wrong? If I use

ALTER TABLE TableB ADD CONSTRAINT fk FOREIGN KEY (CT) REFERENCES TableA(TID) ON DELETE SET NULL

then it works fine.

Thanks,

Ron

ON DELETE RESTRICT is not a valid option in SQL Server. its available in oracle though

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources