| ||||||||||||
I have 2 tables A and Table B respectively: Table A: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aa]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[aa]( [aa1] [int] NOT NULL, [aa2] [int] NULL, [aa3] [int] NULL, CONSTRAINT [PK_aa] PRIMARY KEY CLUSTERED ( [aa1] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END Table B: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bb]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[bb]( [bb1] [int] NOT NULL, [bb2] [int] NOT NULL, [bb3] [int] NOT NULL, CONSTRAINT [PK_bb] PRIMARY KEY CLUSTERED ( [bb1] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_bb_aa]') AND parent_object_id = OBJECT_ID(N'[dbo].[bb]')) ALTER TABLE [dbo].[bb] WITH CHECK ADD CONSTRAINT [FK_bb_aa] FOREIGN KEY([bb2]) REFERENCES [dbo].[aa] ([aa1]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[bb] CHECK CONSTRAINT [FK_bb_aa] IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[FK_BB_AA1]') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].[BB]')) ALTER TABLE [DBO].[BB] WITH CHECK ADD CONSTRAINT [FK_BB_AA1] FOREIGN KEY([BB3]) REFERENCES [DBO].[AA] ([AA1]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [DBO].[BB] CHECK CONSTRAINT [FK_BB_AA1] creating FK_BB_AA1 fails with the following error: Msg 1785, Level 16, State 0, Line 3 Introducing FOREIGN KEY constraint 'FK_bb_aa1' on table 'bb' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Msg 1750, Level 16, State 0, Line 3 Could not create constraint. See previous errors. Msg 4917, Level 16, State 0, Line 1 Constraint 'FK_bb_aa1' does not exist. Msg 4916, Level 16, State 0, Line 1 Could not enable or disable the constraint. See previous errors. How to get this working.