Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Posts
    406
    Thanks
    0
    Thanked 0 Times in 0 Posts

    [ SQL Server 2000 ] How to create a uniquely identifying key

    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?
    Last edited by guelphdad; 07-25-2006 at 06:32 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Posts
    406
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #4
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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.

    ContactTable
    ContactID
    ContactName

    ProjectTable
    ProjectID
    ProjectName

    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:

    RelationshipTable
    ContactID
    ProjectID

    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.

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Posts
    406
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, that helped!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •