#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    23
    Rep Power
    0

    Primary Key .. is it really necessary?


    Lets say my tables look like this:

    PHP Code:
    customer
        id 
    (auto-inc/primary key)
        
    name

    products
        id 
    (auto-inc/primary key)
        
    name

    order
        customer id 
    (index)
        
    product id 
    ..must I have a primary key set for the orders? I can't set either field as a primary key because PKs must be UNIQUE while a customer may place several orders or an item may be purchased by multiple customers.

    Why must there be a primary key for every table? (I have created tables without PKs on multiple occasions) It seems like a waste of space to setup an auto-inc id field when I can't see a use for it.
    Last edited by RyanA; December 24th, 2002 at 07:21 AM.
  2. #2
  3. Super Genius
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2002
    Location
    Orlando, Florida
    Posts
    314
    Rep Power
    13

    Sometimes it is, sometimes it's not.


    Using a primary key whenever possible is a good idea. It doesn't always have to be an auto_incrementing integer either. The point is to give that one record a unique field to set it apart from the rest. Generally, I'll use a non-integer primary key when I need to make sure that a field containing text can't be duplicated (username, email address, etc). I'll always use an integer primary key when I need to refrence something quickly say ... through a url (category id, product id, order id, etc).

    As far as I know, the difference between the a primary key() field and a unique() field is that a primary key automaticly has an index on it.
    Sleep is highly overrated.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Bavaria, Germany
    Posts
    140
    Rep Power
    12
    As kingmike already said, using primary keys wherever possible is a very good idea due to two reason:

    1. Indizes

    This is database depended, some database automaticly generate an index on every primary key column. This gives your table a huge speed increasement (even on tables with little data), but as our data grows, you can't live w/o indizes.

    2. Referential integrity.

    A primary key implies a unique column, so you don't have to worry about duplicate entries on it since it's just another constraint.

    Also you need primary keys in order to create foreign key constraints (just as kingmike described, but e.g. with MSSQL you don't create other primary key's on your referencing columns but a foreign key)
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    I really think this is a question of database design, not just PK usage.

    At one point there was a serious attempt by relational database creators to enforce uniqueness on every table. In other words, their point was: if you can't think of a reason why every row of a table should be unique, then you haven't thought through your database design properly.

    Even though most SQL DB systems don't enforce uniqueness, I make sure that every table I design has a unique key. "Saying something twice doesn't make it any more true." (C.J. Date). Thus having the same identical record more than once doesn't provide any benefit at all, and is a way to invite confusion into your data.

    In your case, I think you are missing a table. You should use the Order table only as a marker for each unique order amount and description, while the orders table would also have a foreign key relationship with an order_details table, which has its own independent primary key, as well as a foreign key matching order IDs and a foreign key matching product IDs. This way, the 'orders' table would handle the main invoice total, while the order_details table would show the line-item details.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo