Click here to Skip to main content
15,922,155 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

ALTER TABLE test3 alter CONSTRAINT const_name FOREIGN KEY REFERENCES test2(test) on delete cascade

i am getting error can you please help me.

thanks in advance..
Posted
Comments
nagendrathecoder 3-Oct-11 7:56am    
Mention what error you are getting.
Om Prakash Pant 3-Oct-11 8:58am    
check the following kb article on managing FKeys:
http://msdn.microsoft.com/en-us/library/ms177463(v=sql.90).aspx

If you'd taken care to read the MSDN page on the ALTER TABLE command (ALTER TABLE[^]) you would have noticed that the commands BNF description does not allow for it. All you can do would be to DROP the constraint an reinstate it with the correct parameters.

Example of adding a constraint to an existing table:
SQL
ALTER TABLE [wm].[TABLE_NAME]  WITH NOCHECK ADD  CONSTRAINT [FK_TABLE_NAME_PARENT_TABLE_NAME] FOREIGN KEY([FOREIGN_KEY])
REFERENCES [wm].[PARENT_TABLE_NAME] ([PRIVATE_KEY])
ON DELETE CASCADE


Cheers!
 
Share this answer
 
We cannot alter the constraint, only thing we can do is drop and recreate.
DROP and ADD constraint script
SQL
ALTER TABLE test3 DROP CONSTRAINT const_name 
GO

ALTER TABLE test3 ADD CONSTRAINT const_name FOREIGN KEY(ColName) REFERENCES test2(test) on delete cascade
GO
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900