SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FK on part of a compound primary key?


FK on part of a compound primary key?

Author
Message
MGS-577297
MGS-577297
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 94
I would like to be able to create a foreign key reference in a child table to part of a compound primary key in the parent table. But I'm pretty sure that's not allowed. If it is will someone point out the proper syntax? If not is there another way I can use a constraint to enforce some referential integrity between part of the key in the parent table and that attribute in the child table? I'm using SQL Server 2000 but will ultimately have to port to 2005 and 2008.

I know I could do this using a trigger but I'd rather use a constraint if possible.

This all comes out of the requirement that I accomodate history (changes over time) in the same table as the active record. I would not have designed it this way but it is a hard specification that I cannot change.

Here's the scenario:

create table t1 col_1 int not null,
col_2 int not null,
constraint primary key (col1,col2)

create table t2 col_A int not null,
col_1 int not null FOREIGN KEY REFERENCES t1(col_1)

but this doesn't work because the foreign key must reference the whole primary key.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162857 Visits: 33199
MGS (12/16/2008)
I would like to be able to create a foreign key reference in a child table to part of a compound primary key in the parent table. But I'm pretty sure that's not allowed. If it is will someone point out the proper syntax? If not is there another way I can use a constraint to enforce some referential integrity between part of the key in the parent table and that attribute in the child table? I'm using SQL Server 2000 but will ultimately have to port to 2005 and 2008.

I know I could do this using a trigger but I'd rather use a constraint if possible.

This all comes out of the requirement that I accomodate history (changes over time) in the same table as the active record. I would not have designed it this way but it is a hard specification that I cannot change.

Here's the scenario:

create table t1 col_1 int not null,
col_2 int not null,
constraint primary key (col1,col2)

create table t2 col_A int not null,
col_1 int not null FOREIGN KEY REFERENCES t1(col_1)

but this doesn't work because the foreign key must reference the whole primary key.


It's the whole key or you can use a unique index and then reference that, but you can't do a constraint against only part of the key or part of a unique index. I had a design similar to what you're doing and had to use triggers. Not the prettiest, but it works.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79004 Visits: 14972
Grant Fritchey (12/16/2008)
MGS (12/16/2008)
I would like to be able to create a foreign key reference in a child table to part of a compound primary key in the parent table. But I'm pretty sure that's not allowed. If it is will someone point out the proper syntax? If not is there another way I can use a constraint to enforce some referential integrity between part of the key in the parent table and that attribute in the child table? I'm using SQL Server 2000 but will ultimately have to port to 2005 and 2008.

I know I could do this using a trigger but I'd rather use a constraint if possible.

This all comes out of the requirement that I accomodate history (changes over time) in the same table as the active record. I would not have designed it this way but it is a hard specification that I cannot change.

Here's the scenario:

create table t1 col_1 int not null,
col_2 int not null,
constraint primary key (col1,col2)

create table t2 col_A int not null,
col_1 int not null FOREIGN KEY REFERENCES t1(col_1)

but this doesn't work because the foreign key must reference the whole primary key.


It's the whole key or you can use a unique index and then reference that, but you can't do a constraint against only part of the key or part of a unique index. I had a design similar to what you're doing and had to use triggers. Not the prettiest, but it works.


Ditto. Triggers aren't always the best but they do work.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
MGS-577297
MGS-577297
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 94
Thanks so much guys. I appreciate your responses!
MGS-577297
MGS-577297
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 94
I forgot to ask if this applies to all versions of SQL Server (higher than 2000). So, to restate the question, can a foreign key be declared in a child table to only part of a compound primary key in the parent table? My coworkers insist that this is possible in Oracle and that surely by now (2005 or 2008 versions) SQL Server has also provided this ability.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79004 Visits: 14972
Yes it applies to all versions of SQL Server. You can only create a foreign key against and ENTIRE primary key OR ENTIRE unique index.

You may be able to do it in Oracle, but there has to be some guarantee of uniqueness otherwise how do you know which parent row the child row(s) relate to?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162857 Visits: 33199
Are they sure Oracle provides enforced (key word) referential integrity on partial keys? I'll have to ask some of our local Oranuts.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162857 Visits: 33199
I just checked and, according to one my local Oracel DBA's, the situation is the same there, so I'm not sure what your Oracle guys are getting at.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
cs_troyk
cs_troyk
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 980
Foreign keys, as you have found out, need to reference a candidate key in the referenced table.

If your design is such that the subset of attributes you wish to "FK to" do not uniquely identify a row in the referenced table (i.e., the subset does not form a candidate key), then this points toward a normalization issue. I would look to see if there is a meaningful set of predicates that could be represented by decomposing the table into two or more new tables, one of which has as its candidate key the subset you're interested in. Then "FK to" that table.

HTH,
TroyK

Edited to add: As others have pointed out, if the attribute(s) of interest that you wish to "FK to" are, in fact, uniquely identifying, simply declare that uniqueness constraint and then you can declare the FK.



sqlvogel
sqlvogel
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3784 Visits: 3713
In Oracle you can implement constraints against materialized views. Haven't tried this out myself but according to the following article it is possible to implement a constraint like the one wanted by MGS.

http://www.dbazine.com/oracle/or-articles/tropashko8

In SQL Server you can achieve a similar result with a user-defined function in a CHECK constraint:

CREATE FUNCTION dbo.fnc_t1_col_1 (@col_1 INT)
RETURNS BIT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN;
RETURN COALESCE((SELECT TOP (1) 1 FROM t1 WHERE col_1 = @col_1),0);
END;
GO

create table t2 (col_A int not null,
col_1 int not null CHECK (dbo.fnc_t1_col_1(col_1)=1));

This isn't a very good alternative to a foreign key because the constraint is only evaluated on INSERT and UPDATE OF t2. It won't stop DELETEs agains t1 even if they violate the intended constraint. A trigger or additional constraint on t1 would be needed to prevent that.

The support of the leading DBMSs for anything other than the most basic integrity constraints is truly abysmal. It is a long standing problem in the SQL world and I would speculate that the data quality failures as a result of not enforcing such constraints may well have cost our industry $millions over the years.

The authors of the SQL standards are partly to blame. For example they created something called a "FOREIGN KEY" constraint, which is not quite what the Relational Model calls a foreign key, adding the pointless restriction that it must match some unique constraint definition (not in fact a candidate key or even a super key but actually an ordered list of columns from a super key).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search