07-25-2006, 07:15 PM
I am using SQL Server 2000 enterprise version. I have to create some tables. One of the table is called orderdetails. It has OrderID which is a integer that uniquely identifies the order numbers. Then I have ProductID which is also integer key that uniquely identifies the product number.
So in this case, are both OrderID and ProductID be primary keys? they say 'uniquely identifies'. What does that mean?
I tried to create both as primary keys but sql server 2000 doesnt let you do that. Only one can be a primary key.
Can some one explain this please?
07-25-2006, 07:31 PM
ProductID would uniquely identify a product but it could certainly appear in more than one order right? If so then it can't be a primary key in that table. And, unless you can ship the same order to more than one customer, or more than once to a same customer, then OrderID is your primary key for that table.
07-25-2006, 08:33 PM
Thanks I understand now. Yes you are right - the productID appears in more than one order
I am looking at a print out of the picture of all the tables I have to create. For the OrderDetails table next or OrderId and ProductID I am seeing a yellow key image. So that means are they both primary keys? Can there be some thing like that?
07-25-2006, 10:35 PM
I'm not sure how SQL Server 2000 handles it, but you can have a "composite key". That means you would have two keys defining a unique record.
I commonly use this with tables that have a many to many relationship.
Let's say I have a table that lists people and a table that lists projects.
You can have multiple people working on one project and a person can work on many projects. You could then create a table that relates the two:
You don't want Bob to be related to project XYZ more than once, so his ContactID and XYZ's ProjectID would be unique when entered as the same record. Bob's ContactID could still exist with another ProjectID and XYZ's ProjectID could still exist with another ContactID though.