Answer the question
In order to leave comments, you need to log in
Two or more Foreign Key fields of one table per ONE field of the main lookup table. Creation error?
There are two tables A and B. Table B is a reference book, it has a primary key with one field. Table A must only have FK keys. The values of fields a1 and a2 do not intersect (but can be NULL). Both fields must take values from the PK of table B.
There is no error when creating the first foreign key.
When creating a second foreign key, an error occurs: "Introducing FOREIGN KEY constraint 'FK_A_B_2' on table 'A' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."
That is, it speaks of looping, although it is essentially not.
There should be two links of one row of table A to table B.
sql server 2008R2
An error occurs when creating a foreign key of the second field of table A - a2 on the PK of table B.
CREATE TABLE A
(
a1 int NULL,
a2 int NULL,
a3 int NULL
)
GO
CREATE TABLE B
(
b1 int IDENTITY (1,1) NOT NULL,
b2 int NULL,
PRIMARY KEY(b1)
)
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_A_B]') AND parent_object_id = OBJECT_ID(N'[dbo].[A]'))
ALTER TABLE dbo.A DROP CONSTRAINT FK_A_B
GO
ALTER TABLE dbo.A WITH CHECK ADD CONSTRAINT FK_A_B FOREIGN KEY(a1)
REFERENCES dbo.B (b1) ON DELETE SET NULL
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_A_B_2]') AND parent_object_id = OBJECT_ID(N'[dbo].[A]'))
ALTER TABLE dbo.A DROP CONSTRAINT FK_A_B_2
GO
/*После следующих строк возникает ошибка*/
ALTER TABLE dbo.A WITH CHECK ADD CONSTRAINT FK_A_B_2 FOREIGN KEY(a2)
REFERENCES dbo.B (b1) ON DELETE SET NULL
GO
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question