December 16, 2008 at 7:23 pm
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.
December 16, 2008 at 8:27 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2008 at 7:15 am
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
Consultant - Straight Path Solutions
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
December 17, 2008 at 10:35 am
Thanks so much guys. I appreciate your responses!
December 17, 2008 at 3:06 pm
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.
December 17, 2008 at 6:17 pm
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
Consultant - Straight Path Solutions
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
December 18, 2008 at 5:28 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2008 at 7:47 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2008 at 6:54 pm
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.
December 20, 2008 at 3:07 pm
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).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply