Composite Foreign Key

[scoarescoare]

I have been looking and looking and cannot come up with a clear, solid solution.

I have a table and that table uses 3 columns to create a composite PK. I need to use this PK as a foreign key in another table. That means I need to add these three columns to the child table which...quite frankly..i think is kind of a pain. I cannot believe there is not a simpler method. If anyone has any ideas or alternative solutions, pleaes give me a hollar. Thanks a lot.

[frozenmist]

Hi,
I dont know how good this is but why dont you try using an identity column.
For each combination of the three keys the identity column will have a value.
Pust this ID column value in the child table.
Just a thought.
Hope I could give something meaningful
Cheers

[chandu031]

I have been looking and looking and cannot come up with a clear, solid solution.

I have a table and that table uses 3 columns to create a composite PK. I need to use this PK as a foreign key in another table. That means I need to add these three columns to the child table which...quite frankly..i think is kind of a pain. I cannot believe there is not a simpler method. If anyone has any ideas or alternative solutions, pleaes give me a hollar. Thanks a lot.

Hi,

You can have a surrogate key on the parent table which as frozen pointed out would be an identity column. This surrogate key can then be used as a foreign key in the child table.

[scoarescoare]

Right..thanks.. I see what you two are saying. However..wouldn 't this allow for the three columns to have redundant data and not be unique? I kind of needs this composite PK be unique. Any ideas?

[pradeep kaltari]

Right..thanks.. I see what you two are saying. However..wouldn 't this allow for the three columns to have redundant data and not be unique? I kind of needs this composite PK be unique. Any ideas?

Hi,
You can still have a unique constraint on the combination of those three columns and the identity column can be the primary key.
[code=sql]
ALTER TABLE <table_name> ADD CONSTRAINT unique_col UNIQUE(col1,col 2,col2)
[/code]

Regards,
Pradeep

[scoarescoare]

yes yes yes! that is perfect. Thanks for opening up my eyes everyone. Really, thanks a lot!