Can a Compound key be set as a primary key to another table?

The short answer is, No - can't do that. All of the columns in the PK (or other alternate key) must appear in the FK definition. Also remember that a table can have multiple keys - referred to as Candidate Keys (sometimes alternate keys).

The primary key is simply the "best" key for your design/use (normally the narrowest one - smallest in byte size). We prefix the name of these other unique indices as "AK_{name}" - AK = Alternate Key What we do under this circumstance is one of two things: define a synthesized PK being an "Identity" column, and then add a Unique Index to the compound key - thus having two "keys" on the table. All child tables then define the FK as pointing to the synthetic Identity PK column Leave the compound key as is.

Define it as the PK on that master table, and make all FKs have the same definition Generally it isn't a good idea to have multiple tables with the same PK definition (that is, PKs that are also FKs to another table). I say "generally" - it is important to understand the definition of your Entities So why use #1? The question I ask myself is whether the compound key is really the data the defines the row?

Is that compound key really THE definition of a row or is it more of an FK to some other data? If it is more of an FK then I create the synthesized PK(Identity). Is an Order really the books that a client owns?

An order may cause a client to own a new book - but they may not be the same thing Having the synthesized PK offers a few more choices down the road in future years. If the relationship needs to change in your Client_Books table then you might insulate changes to other tables For instance - what if a customer can own more than one copy of a book - how will you implement that? With the synthesized key you could simply remove the Unique index on the compound-key and leave it as a simple FK, then the Order_Details table would simply represent when a customer purchased their second copy.

If however you had taken the compound key on Orders route, then you'd have to figure out how to redefine Order_Detail as well as Client_Books (and any other FKs that pointed to Order_Detail) I would also ask you to evaluate whether an Order_Detail is really a Client_Book? Normally an Order causes the client to come into possession of a new book. I think of Orders as being independent of the inventory - therefore the PK on Order_Detail should not be the PK on Client_Books, Order_Detail should probably have it's own independent PK.

The short answer is, No - can't do that. All of the columns in the PK (or other alternate key) must appear in the FK definition. Also remember that a table can have multiple keys - referred to as Candidate Keys (sometimes alternate keys).

The primary key is simply the "best" key for your design/use (normally the narrowest one - smallest in byte size). We prefix the name of these other unique indices as "AK_{name}" - AK = Alternate Key. What we do under this circumstance is one of two things: define a synthesized PK being an "Identity" column, and then add a Unique Index to the compound key - thus having two "keys" on the table.

All child tables then define the FK as pointing to the synthetic Identity PK column. Leave the compound key as is. Define it as the PK on that master table, and make all FKs have the same definition.

Generally it isn't a good idea to have multiple tables with the same PK definition (that is, PKs that are also FKs to another table). I say "generally" - it is important to understand the definition of your Entities. So why use #1?

The question I ask myself is whether the compound key is really the data the defines the row? Is that compound key really THE definition of a row or is it more of an FK to some other data? If it is more of an FK then I create the synthesized PK(Identity).

Is an Order really the books that a client owns? An order may cause a client to own a new book - but they may not be the same thing. Having the synthesized PK offers a few more choices down the road in future years.

If the relationship needs to change in your Client_Books table then you might insulate changes to other tables. For instance - what if a customer can own more than one copy of a book - how will you implement that? With the synthesized key you could simply remove the Unique index on the compound-key and leave it as a simple FK, then the Order_Details table would simply represent when a customer purchased their second copy.

If however you had taken the compound key on Orders route, then you'd have to figure out how to redefine Order_Detail as well as Client_Books (and any other FKs that pointed to Order_Detail). I would also ask you to evaluate whether an Order_Detail is really a Client_Book? Normally an Order causes the client to come into possession of a new book.

I think of Orders as being independent of the inventory - therefore the PK on Order_Detail should not be the PK on Client_Books, Order_Detail should probably have it's own independent PK.

1 Well said. Probably one of the most complete answers to a question I've seen on SO so far. – Kenaniah May 26 '10 at 17:36 thanks very much for this ended complete answer.. – Nikos May 28 '10 at 18:47.

You are walking on a dark and dirty path. Don't do this. Stick to best practices - use a simple primary key on the table, and then have a foreign key if necessary to other tables.

Seems you know what is wrong with that idea... Thank you very much... – Nikos Feb 22 '10 at 21:22.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.