Monday, 2 February 2009

SQL Add/Remove constraints, foreign key

IF NOT EXISTS( SELECT 1 FROM information_schema.table_constraints WHERE table_name = 'table_name' AND constraint_name = 'contraint_name' )
ALTER TABLE [dbo].[table_name] 
ADD CONSTRAINT contraint_name FOREIGN KEY 
 (
  [Id]
 ) REFERENCES [table2_name] (
  [Id]
 ) 
GO

To allow cascade delete update the last line

 ) REFERENCES [table2_name] (
  [Id]
 ) ON DELETE CASCADE 
GO

To remove constraint

ALTER TABLE table_name DROP CONSTRAINT [constraint_name]

No comments:

Post a Comment