Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FK on part of a compound primary key? Expand / Collapse
Author
Message
Posted Tuesday, December 16, 2008 7:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 24, 2013 2:53 PM
Points: 16, 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.
Post #620863
Posted Tuesday, December 16, 2008 8:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #620895
Posted Wednesday, December 17, 2008 7:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 10,191, Visits: 13,115
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

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
Post #621200
Posted Wednesday, December 17, 2008 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 24, 2013 2:53 PM
Points: 16, Visits: 94
Thanks so much guys. I appreciate your responses!
Post #621449
Posted Wednesday, December 17, 2008 3:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 24, 2013 2:53 PM
Points: 16, 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.
Post #621658
Posted Wednesday, December 17, 2008 6:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 10,191, Visits: 13,115
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

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
Post #621730
Posted Thursday, December 18, 2008 5:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #621951
Posted Thursday, December 18, 2008 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #622091
Posted Thursday, December 18, 2008 6:54 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:09 PM
Points: 1,325, Visits: 795
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.



Post #622555
Posted Saturday, December 20, 2008 3:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 448, Visits: 3,356
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).


David
Post #623516
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse