December 16, 2009 at 4:02 am
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 columnn 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?
Any ideas appreciated.
Thanks
December 16, 2009 at 4:22 am
From the error message that you specified it seems that those 2 columns are not the columns that the primary key or any unique index. Could it be that those columns are part of a unique index/primary key?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 16, 2009 at 4:59 am
Hi,
They are definitely the only columns constituting the primary key.
I've checked this several times - only these 2 columns have the yellow key icon and "PK" next to them.
December 16, 2009 at 5:16 am
Can you check it using the sp_helpindex procedure? Just run it with the table's name and check the columns' names for the primary key and all unique indexes.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 16, 2009 at 5:21 am
returns:
PK_Line_Partitionedclustered, unique, primary key located on PS_LineCreatedAt CreatedAt, LineId
I am beginning to think it is because these columns are on a partition scheme (PS_LineCreatedAt)
whereas the same columns on the target table where I'm trying to add the foreign key is not on this partition scheme.
December 16, 2009 at 7:01 am
The script bellow shows that you can create a foreign key that references a partitioned table. Can you post the script that you are using to create the foreign key?
create database FKTest
go
use FKTest
go
CREATE PARTITION FUNCTION fnpartitionRange (INT)
AS RANGE LEFT FOR VALUES (1,10)
go
CREATE PARTITION SCHEME SCPartitionScheme AS
PARTITION fnPartitionRange
ALL TO ([PRIMARY])
go
CREATE TABLE MyPartitionedTable
(
i INT not null,
dt datetime not null,
filler varchar(200)
)
ON
SCPartitionScheme (i)
go
alter table MyPartitionedTable add constraint PK_MyPartitionedTable primary key (i, dt)
go
--insert some test data
insert into MyPartitionedTable (i, dt, filler)
select 0, '20090101', 'aaa'
union
select 5, '20090510', 'bbb'
union
select 15, '20091216', 'ccc'
go
create table AnotherTbl (i int not null, dt datetime not null, AnotherCol varchar(10))
go
--creating the foreign key
alter table AnotherTbl add constraint FK_AnotherTbl_MyPartitionedTable foreign key (i, dt) references MyPartitionedTable (i, dt)
--check the foreign key
insert into AnotherTbl (i, dt, AnotherCol) values (0, '20090101', 'WhatEver')
--This one should fail
insert into AnotherTbl (i, dt, AnotherCol) values (23, '20090101', 'WhatEver')
--cleanup
use master
go
drop database FKTest
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 16, 2009 at 8:30 am
Interesting, thanks for saving me the trouble of doing that - although could it be the fact that the partitions are on different filegroups in my case?
The code i'm using to create the key is:
ALTER TABLE Y
ADD CONSTRAINT [FK1]
FOREIGN KEY(LineId,LineCreatedAt)
REFERENCES X(LineId, CreatedAt)
December 16, 2009 at 1:59 pm
I don’t think so. You can modify my script to create a database with few files and then modify the partition schema to use few files instead of 1 file as it does currently. I expect that it will still work without errors after this modification.
If you still have problems creating the foreign key, can you post a script that creates the database, your partition function and schema, the partitioned table and the referencing table? This might help finding your error.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 17, 2009 at 7:37 am
Looking into this further, it appears to be an issue with the ORDER of the columns which constitute the primary key.
Thus the following:
ALTER TABLE Y
ADD CONSTRAINT [FK1]
FOREIGN KEY(LineCreatedAt,LineId)
REFERENCES X(CreatedAt, LineId)
is allowed whereas
ALTER TABLE Y
ADD CONSTRAINT [FK1]
FOREIGN KEY(LineId, LineCreatedAt)
REFERENCES X(LineId, CreatedAt)
is not.
The order of the columns when specifying the foreign key is just as important as when specifying the primary key, but I suppose that should be obvious.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply