need help in applying foreign key constraint in the table

Question

need help in applying foreign key constraint in the table

shivanee.chikhalikar

SSC Enthusiast

Points: 185
More actions
July 13, 2010 at 4:56 am

#221205

Hello all,
I am adding the foreign key using the following query
ALTER TABLE RepairCategoryDetail
ADD CONSTRAINT FK_RepairCategoryDetail FOREIGN KEY (CategoryId)
REFERENCES RepairCategoryMaster(CategoryId);
But it is giving me an following error
There are no primary or candidate keys in the referenced table 'RepairCategoryMaster' that match the referencing column list in the foreign key 'FK_RepairCategoryDetail'.
But, in master table there is composit key of CustNoCorp and Categoryid. And Category id is the primary key having identity on.
Also the Detail table having a Category Id and this field is also a primary key.
Could any one please help me to resolve this problem?
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

shivanee.chikhalikar (7/13/2010)
Hello all,
I am adding the foreign key using the following query
ALTER TABLE RepairCategoryDetail
ADD CONSTRAINT FK_RepairCategoryDetail FOREIGN KEY (CategoryId)
REFERENCES RepairCategoryMaster(CategoryId);
But it is giving me an following error
There are no primary or candidate keys in the referenced table 'RepairCategoryMaster' that match the referencing column list in the foreign key 'FK_RepairCategoryDetail'.
But, in master table there is composit key of CustNoCorp and Categoryid. And Category id is the primary key having identity on.
Also the Detail table having a Category Id and this field is also a primary key.
Could any one please help me to resolve this problem?
Thanks

The error is pretty explicit and you need to examine your structures based on what it says. If the RepairCategoryMaster table has a compound primary key consisting of two columns, then your referencing foreign key needs to also consist of two columns. I'm a bit confused because your description refers to two different keys ono the RepairCategoryMaster table. Do you have a unique index AND a primary key or is something else going on that I'm missing.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Paul White SSC Guru Points: 150468 More actions · Answer 2

shivanee.chikhalikar (7/13/2010)
But, in master table there is composit key of CustNoCorp and Categoryid. And Category id is the primary key having identity on.

Giving a column the IDENTITY property does not automatically create a unique index or a primary key constraint.

The easiest way to get a useful answer would be to post example CREATE TABLE and CREATE INDEX statements for both tables, together with a small sample of data that illustrates the relationship between the two.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

shivanee.chikhalikar SSC Enthusiast Points: 185 More actions · Answer 3

Hello All,

In RepiarCategoryMaster, the primary key constraint of this table is PK_RepairCategoryMaster(CustNoCorp,CategoryID) and identyity of categoryid is "Yes"

In RepairCategoryDetail table (where I want to add the foriegn key for CategoryID), the primary key constraint of this table is PK_RepairCategoryDetail(RepairID,CategporyID).

I am writing a alter query like the following:

ALTER TABLE RepairCategoryDetail

ADD CONSTRAINT FK_RepairCategoryDetail FOREIGN KEY (CategoryId)

REFERENCES RepairCategoryMaster(CategoryId);

GO

and I am getting this error:

There are no primary or candidate keys in the referenced table 'RepairCategoryMaster' that match the referencing column list in the foreign key 'FK_RepairCategoryDetail'.

Paul White SSC Guru Points: 150468 More actions · Answer 4

You need to add a UNIQUE index or constraint to the RepairCategoryMaster table. The index or constraint should be on the CategoryID column only.

You should also read up on foreign key relationships to improve your understanding.

See the following link: http://msdn.microsoft.com/en-us/library/ms175464.aspx

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

Yep, what he said. Just because the column is part of the primary key does not mean you can only use that. I used to have a sign up on the wall for my development team. It read "The key, the whole key and nothing but the key, so help me Codd." When referring to the key, it's all or nothing. But, as Paul points out, you can use a unique index to arrive at the same place, but even there, it's the whole index or nothing.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning