Is a primary key absolutely necessary?

Posted on 2007-10-16
Last Modified: 2008-01-09
I have a table of data that looks something like this:
11052985 SF P C 7.15 2 139000 135785.41
51054524 ZZ P A 5.55 1 122000 135675.43
21044984 SD Y G 2.35 1 123400 131175.45
11052985 ZZ P C 7.15 2 139000 135785.41
and so on.
There is no one field that is a unique value, e.g. notice that the first field in the 1st row and the 4th row
is the same.
I haven't assigned a primary key to this table. It seems it would have to be something artificial like this:
1 11052985 SF P C 7.15 2 139000 135785.41
2 51054524 ZZ P A 5.55 1 122000 135675.43
3 21044984 SD Y G 2.35 1 123400 131175.45
4 11052985 ZZ P C 7.15 2 139000 135785.41

Must I have a primary key?
What are the consequences of not having one?
If I must, is the way to do as I describe above?
Question by:allelopath
    LVL 17

    Accepted Solution

    Strictly speaking, it is not - you can have a table without a primary key. But, everything you want to do on that table will be _much_ easier if you do have a primary key. You'll have a narh time identifying the records... At least think of adding NON UNIQUE indexes, if you do not want to have a PRIMARY KEY.

    On the other hand, the primary key can consist of multiple columns as any other index...
    LVL 17

    Expert Comment

    > a narh time

    a hard time
    LVL 14

    Expert Comment

    I agree with leannonn.
    While you may not see a need for one at the moment if in the future you need to link to another table or add search facilities etc it will be much easier and more efficient if you have a primary Key.

    Assisted Solution

    It's difficult to say for sure without knowing what the full requirements are, but PK's are not _absolutely_ necessary.  The problem comes when you need to link the records between different tables - how else do you refer to any particular row?

    It's not uncommon to add a completely arbitrary key in the way you describe, although if the database has a high transaction-rate or is distributed then there are all sorts of considerations.  This would affect wether you use a sequential key like this, or perhaps GUIDs, or some other mechanism.

    If two columns define a record then you can use compound indexes, but this makes links very complex.

    Will this be a single table purely for storing some data, or will you be splitting/linking/exporting/importing/etc this data from/to somewhere?
    LVL 17

    Assisted Solution

    By the way, you can always include an additional field in the table description, like:
    CREATE TABLE yourTable(
      your_field_1 ...
      your_field_2 ...

    This gives you a primary key you don't have to worry about. It will generate values on it's own - no need to enter any data into the `id` field and you don't have to use it in your queries.
    LVL 1

    Author Comment

    The database is being built by a java program which is reading text file and writing to the db.

    Let's call the first field , e.g. 11052985  in the 1st and 4th rows, the 'index number'
    The primary use of the db will be to pull all rows of a given index number (or index numbers), e.g.
    SELECT * FROM mytable WHERE indexnumber='11052985'

    There should not be any duplicate rows. (I don't think this would be harmful, but it would be meaningless)

    What is a non-unique index? From the sound of it, that may be the way to go. I don't see much about it on the www.

    Expert Comment

    A column can be indexed, but non-unique.  This is primarily to speed up filtering and linking.
    LVL 17

    Assisted Solution

    > What is a non-unique index?

    An index on any column(s) but does not require the uniqueness of data - you can have duplicates. Usually used for speeding up table queries.

    More info:
    LVL 22

    Expert Comment

    You can also use combinations of columns as a primary key.  

    Team        Number       Name
    Steelers        7             Ben Roethlisberger
    Jets               7             Ben Graham

    In this table there are many Steelers players, and many players with the number 7, but each combination of (Team,Number) is unique.  

    ALTER TABLE FootballPlayers add PRIMARY KEY (Team,Number)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now