SQL Server Foreign Key Problem

Question

Hi,

I am trying to create a foreign key constraint consisting of an int column and a datetime column (both not null).

I am getting the error "There are no primary or candidate keys in the referenced table X that match the referencing column list in the foreign key FK".

Normally this error means that the column(s) in the target table is not unique. However it is the primary key so it is definitely unique.

The table which the foreign key is referencing is partitioned across multiple file groups - whereas the table on which the foreign key is being created resides on the primary file group. Could this be the problem? Can datetimes be part of a foreign key?

Table X:

LineId (int not null) (PK)
CreatedAt (datetime not null) (PK)
Message (nvarchar(max))
userId (int not null)

Table Y:

LineId (int not null) (PK) (FK)
SId (int not null) (PK)
LineCreatedAt (datetime not null) (FK)
CreatedAt (datetime not null)

SQL command:

ALTER TABLE Y
ADD CONSTRAINT [FK1] 
FOREIGN KEY(LineId,LineCreatedAt)
REFERENCES X(LineId, CreatedAt)

Any ideas appreciated.

Thanks

Answer 1

No, the error you receive does not mean that the columns are not unique. It means that the columns you're referencing to, do not match with the columns in your foreign table. Can you display the 2 tables and your FK definition ?

Answer 2

I have tested this in SQL2005 Dev edition, with both tables X and Y created on PRIMARY file group. I had no trouble (as I suspected I wouldn't) in creating the foreign key you describe. I also doubt (but I'm a lot less sure about) that having the table partitioned on multiple file groups should affect this in this manner.

Can you please provide the SQL you used to create your tables? This may shed some light on the cause of your problems, particularly if there are some wierd settings which are active (and SQL-Server is chock full of wierd settings.)

Answer 3

Apparently the problem was in the ordering of the columns within the keys. Since the primary key of table X was (CreatedAt,LineId), changing the script to:

ALTER TABLE Y
ADD CONSTRAINT [FK1] 
FOREIGN KEY(LineCreatedAt,LineId)
REFERENCES X(CreatedAt,LineId)

worked successfully. A bad mistake to make, but when looking at the object explorer you only see the key columns, not their order within the key.