AutoNumber ID column necessary in link table?

[JohnnyD]

Hi,

I am using SQL Server 2005. I have two tables:

Products - has the following columns:

PK_ProdID
ProdName
etc..

and ProductItems - has the following columns:

PK_ItemID
ItemName
etc...

Any product can have any combination of productitems. So I have a third table which stores these links between products and their component items:

ProductLink - has the following columns:

FK_ProdID
FK_ItemID
Quantity

My question is - do I need to have an autonumber ID column on this third table as a primary key? I was thinking that, each product can only have each item in there once so the primary key would be a composite consisting of the two foreign key columns.

Would that be right?

Thanks,

John.

[jcaccia]

FK_ProdID is a copy of PK_ProdID from your products table, same with FK_ItemID. They can't be autonumbers, just use the ones created for the products and items tables.

[vuyiswam]

on the Third table you must add an identity field that will be the primary key of the table, because the Foreign keys will not be a primary key of this table. if your follow the normal forms, you will remember that every table must have a key that will identify each row uniquely. do not be deceive by the uniqueness that you see currently.

[jcaccia]

... because the Foreign keys will not be a primary key of this table.

Why not? Why can't FK_ProdId and FK_ItemID be the primary key?

[vuyiswam]

They can be Composite key, but it is good that the table has its own primary key

[jcaccia]

They can be Composite key, but it is good that the table has its own primary key

A composite key in this case is fine; is there a good reason for that? I mean a really good one, not that a friend told you it would be better.

[vuyiswam]

A composite key in this case is fine; is there a good reason for that? I mean a really good one, not that a friend told you it would be better.

jcaccia wrote :

A composite key in this case is fine; is there a good reason for that?

A Composite is use if a simple field cannot identify a row uniquely in a table.


jcaccia wrote :
not that a friend told you it would be better.

i dont know what you were trying to say here

[jcaccia]

A Composite is use if a simple field cannot identify a row uniquely in a table.

That's precisely the case here.

[JohnnyD]

I think the confusion has occured because I didn't explain myself very well. These two tables are to store lists of products and the components that make up a product.

So any product can be made up of any number of product items, but obviously a single product wouldn't have the same product item twice.

Why not? Why can't FK_ProdId and FK_ItemID be the primary key?

This is what I was thinking. I guess I am at the point now where I'm learning to restrain myself from having an autonumbered ID column on every single table. I have been reading a lot about when to use natural primary keys and when to use surrogate keys and I think in this case, making a composite PK out of the two FKs makes sense because it will enforce the rule that one product can't have the same product item twice. The only thing I'm not sure of is if this makes joins on these tables more complicated or not.

Thanks for the input guys.

[dglienna]

No key means that it isn't INDEXED, which means searches will take a lot longer, as it can't store the INDEX from related searches