Foreign Key Relationships to a Compound Primary key

Question

Post reply

Foreign Key Relationships to a Compound Primary key

Paul McCurdy

Ten Centuries

Points: 1377
More actions
August 31, 2009 at 12:06 pm

#210147

I have a number of 'reference' tables where the primary key is defined using a combination of a 'key' value plus a column I called currentRow, which can be Y or N. If a relationship within a row is changed, I change the old row to currentRow = 'N' and insert a new row with the same 'key' with an indicator of 'Y'. Example
Region table has columns: regionKey, currentRow (Y or N), effectiveDate
Unit table has columns: unitKey, regionKey, currentRow (Y or N), effectiveDate
So if there is a change in relationship between the unit and its region, I'd end up with 2 records in the Unit table:
Record 1 -
unitKey = 12
regionKey = 1
currentRow = 'N'
effectiveDate = a date
Record 2 -
unitKey = 12
regionKey = 2
currentRow = 'Y
effectiveDate = a date
I built the PKs on the 2 tables as unitKey+currentRow, and regionKey+currentRow. But, I can't build a FK relationship between them.
Has anyone got any thoughts on this, or can recommend a good source of information? Short of dropping the currentRow indicator from the tbale - and losing history of changes (guess they could go into a history table) - I'm stumped.
Any help would be appreciated!

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i don't use complex foreign keys , but here is an example i whipped up for the syntax:

this foreign key references two columns in the parent table...limit is 16 i think, same as a constraint?

create table ComplexPK(

Column1 int,

Column2 int,

CONSTRAINT [PK__ComplexPK] PRIMARY KEY CLUSTERED (Column1,Column2) )

create table ComplexChild(

ChildId int identity(1,1) primary key,

Column1 int,

Column2 int,

CONSTRAINT [FK__ComplexPK] FOREIGN KEY (Column1,Column2) REFERENCES ComplexPK(Column1,Column2) )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 2

You really don't want to be changing primary key values. It's going to lead to all sorts of issues. A design I've used multiple times to track versions basically results from creating a version table. Each data change is an insert. Each set of inserts works off a single ID value (just an identity column) that increases each time someone inserts new data. You don't have to put the version number into the PK, but use the rest of the key for FK relationships and then simply select max value where the version number is equal to or less than a reference version. This allows you to get a precise history to a point in time, as well as getting the latest records.

"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 McCurdy Ten Centuries Points: 1377 More actions · Answer 3

I see where using a version id vs Y/N indicator makes more sense, but am confused as to how the FK relationship would work. Let's say the region table also has a relationship to say a country table. If a region is defined to include a different country wouldn't I be back where I started trying to define the FK

Unit Table

verId UnitKey RegionKey EffectiveDate

1 1 1 2009-08-01

2 1 2 2009-08-15

Region Table

verId RegionKey CountryKey EffectiveDate

1 1 1 2009-08-01

2 1 2 2009-08-10

If FK on Unit table to Region Table is on RegionKey, and region table is updated for new relationship to country, how can it maintain integrity? I'm getting myself totally confused now :unsure:

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 4

Paul McCurdy (8/31/2009)
I see where using a version id vs Y/N indicator makes more sense, but am confused as to how the FK relationship would work. Let's say the region table also has a relationship to say a country table. If a region is defined to include a different country wouldn't I be back where I started trying to define the FK
Unit Table
verId UnitKey RegionKey EffectiveDate
1 1 1 2009-08-01
2 1 2 2009-08-15
Region Table
verId RegionKey CountryKey EffectiveDate
1 1 1 2009-08-01
2 1 2 2009-08-10
If FK on Unit table to Region Table is on RegionKey, and region table is updated for new relationship to country, how can it maintain integrity? I'm getting myself totally confused now :unsure:

It depends on how you want to handle things, what the primary key values are. I've done it with the PK/FK including the version ID and then you have to insert the new records in the Unit table and a new one in the Region table to reflect the change. But you can simply store the version id in the Region table, independent of the Unit table, adding a new row with a new version. I showed a little bit about how this would work here and here[/url].

"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 McCurdy Ten Centuries Points: 1377 More actions · Answer 5

Very cool articles Grant - I think these will help me a lot develop a solution that will work for us.

Thanks a lot!