Q
Q
qazxdr2014-02-12 13:11:03
SQL
qazxdr, 2014-02-12 13:11:03

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 question

Ask a Question

731 491 924 answers to any question