Monday, May 30, 2011

Composite foreign key and order of columns

A simple database related thing which everybody knows.But I would like to post because this took around 4 hrs in our team.Let me come to the scenario. We have a database which has some primary and foreign keys.Its like a normal DB. Now we are doing some enhancements into it which brought a new column to all the primary keys .ie Composite primary key.

This introduces changes in all the other tables which refer using foreign keys.Since there are a number of tables in the database and all the table creation scripts were checked into TFS, we assigned some people to change the foreign key references.They were really new to project and they did their job in less time.We didn’t even run the scripts.But problems started when we run the changed table creation scripts.The error was

“There are no primary or candidate keys in the referenced table 'dbo.master' that match the referencing column list in the foreign key 'FK_Detail_Master'.”

For more details see the below scripts.

create table [master] (
Column1 int,
ColumnNew int
primary key(Column1,ColumnNew)
CREATE TABLE [dbo].[Detail](
Column1 [int] NULL,
ColumnNew [int] NULL,
[Column2] [nchar](10) NULL
ALTER TABLE [dbo].[Detail] WITH CHECK ADD CONSTRAINT [FK_Detail_Master] FOREIGN KEY([Column1], [ColumnNew])
REFERENCES [dbo].[master] ( [ColumnNew],[Column1])
ALTER TABLE [dbo].[Detail] CHECK CONSTRAINT [FK_Detail_Master]

In the first look its little difficult to identity the issue.But the issue is simple.The order of fields specified is wrong.The Column1 should come as follows.

ALTER TABLE [dbo].[Detail]  WITH CHECK ADD  CONSTRAINT [FK_Detail_Master] FOREIGN KEY([Column1], [ColumnNew])
REFERENCES [dbo].[master] ([Column1], [ColumnNew])

It was a typo and may be overconfidence.But it costs a lot. We consulted with Dimitri Furman a Microsoft consultant for database and he confirmed the behavior.

No comments: