Multiple Foreign Keys to Same Table

Question

I am trying to create FK relationships from several columns in one table to the PK of a second table.  As an example, suppose several columns in one table can hold references to the "XXX_Color" table, which in turn holds values such as 'Red', 'Blue', 'Green', etc.

The first one (eg. FK_Color_1) is created, but subsequent efforts result in this message:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Color_2". The conflict occurred in database "XXX_Dev", table "dbo.XXX_Color", column 'ColorId'.

My SQL is like so:

ALTER TABLE [dbo].[XXX_TableOne]
WITH CHECK ADD CONSTRAINT [FK_Color_2]
FOREIGN KEY([ColumnWithColorReference])
REFERENCES [dbo].[XXX_Color] ([ColorId])

I have checked the datatypes of the FK's and the PK of the "Color" table and they are the same (int).  Can someone point out to me what I need to be doing or checking?  Thanks.

---

Echo Train Nashville TN

Answer 1

It is telling you that you have a value in xxx_TableOne.ColumnWithColorReference that does not exist in XXX_Color.ColorId

---

Chuck

Answer 2

Does the column 'ColumnWithColorReference' already has data in it? If yes then the value may not exist in primary key column value.

Rajitha.

---

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 3

Thanks for the ideas.

The XXX_Color table is populated.  XXX_TableOne.ColumnWithColorReference is a new column, but the table is not, so the values in that column are all null.  ColumnWithColorReference is defined as nullable.

Imagine XXX_TableOne as a table of the colors people wear each day: 'PantsColor', 'ShirtColor', 'JacketColor' to which I have now added 'TieColor'.  Not everyone wears a tie, so 'TieColor' would not always have a value.  Plus, it would not have been recorded for the already existing entries.  Am I trying something that won't work?

---

Echo Train Nashville TN

Answer 4

It is not a problem that you have NULL's in XXX_TableOne.ColumnWithColorReference.  It is not a foreign key violation if the XXX_TableOne.ColumnWithColorReference column contains NULL, it is only a violation if some row in XXX_TableOne has a non Null value in ColumnWithColorReference and that value is not in dbo.XXX_Color.ColorId.

Try running

Select * From [dbo].[XXX_TableOne] t
Where Not Exists (Select * From [dbo].[XXX_Color] c
  Where t.ColumnWithColorReference = c.ColorId)
And t.ColumnWithColorReference Is Not Null;

That should show you the row(s) in XXX_TableOne that are causing you the problem.

Tom

Answer 5

I am trying to create FK relationships from several columns in one table to the PK of a second table.  As an example, suppose several columns in one table can hold references to the "XXX_Color" table, which in turn holds values such as 'Red', 'Blue', 'Green', etc.

The first one (eg. FK_Color_1) is created, but subsequent efforts result in this message:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Color_2". The conflict occurred in database "XXX_Dev", table "dbo.XXX_Color", column 'ColorId'.

My SQL is like so:

ALTER TABLE [dbo].[XXX_TableOne]
WITH CHECK ADD CONSTRAINT [FK_Color_2]
FOREIGN KEY([ColumnWithColorReference])
REFERENCES [dbo].[XXX_Color] ([ColorId])

I have checked the datatypes of the FK's and the PK of the "Color" table and they are the same (int).  Can someone point out to me what I need to be doing or checking?  Thanks.

---

Echo Train Nashville TN

each new foreign key definition requires a new name, please change the name in ADD CONSTRAINT[FK_Color_2] each time you add a new foreign key constraint.

or do I understand you wrong and you want to create mutliple foreign keys constraint for the same column ColumnWithColorReference?

that would not make sense and could you therefore explain the scenario you want to implement multiple foreign keys?

Answer 6

I think it might help if you posted the actual DDL of the tables and some sample data, as your examples have been santised too far and may be masking the problem, or the question!

There's certainly no problem with having multiple FKs on a table, or even the scenario where no TieColor has been added for existing entries, see this mock up :

if object_id('Clothes','U') is not null drop table Clothes
go
if object_id('Color','U') is not null drop table Color
go
create table Color (ColorID int primary key, color varchar(10))

insert into Color select 1, 'Red'
insert into Color select 2, 'Green'
insert into Color select 3, 'Blue'
insert into Color select 4, 'Yellow'

create table Clothes (
	UniqueID int primary key, 
	PantsColor int ,
	ShirtColor int ,
	TieColor int
	)

insert into Clothes select 1,1,1,null
insert into Clothes select 2,1,1,null
insert into Clothes select 3,2,1,3
insert into Clothes select 4,1,1,2

alter table Clothes with check add constraint FK_Color1 foreign key (PantsColor) references Color(colorID)
alter table Clothes with check add constraint FK_Color2 foreign key (ShirtColor) references Color(colorID)
alter table Clothes with check add constraint FK_Color3 foreign key (TieColor) references Color(colorID)

Answer 7

oldjeep and Eshani Rao nailed it from the start.  A handfull of the rows (in a large table) were populated with 0 rather than NULL in some columns during the development process.  Tom Cooper's SQL showed this to me.  Thanks to everyone who chimed in.

---

Echo Train Nashville TN