I'm creating a database table and I don't have a logical primary key assigned to it. So, I'm thinking about leaving it without a primary key, but I'm felling a bit guiltly about it. Should I?
Should each and every table have a primary key?
EDIT: Okay, okay... I've created the primary key! Are you happy now? :)
Short answer: yes.
Long answer:
In MySQL, the InnoDB storage engine always creates a PRIMARY KEY if you didn't specify it explicitly, thus making an extra column you don't have access to.
Note that a PRIMARY KEY can be composite.
If you have a many-to-many link table, you create the PRIMARY KEY on all fields involved in the link. Thus you ensure that you don't have two or more records describing one link.
Besides the logical consistency issues, most RDBMS engines will benefit from including these fields in an UNIQUE index.
And since any PRIMARY KEY involves creating a UNIQUE index, you should declare it and get both logical consistency and performance.
See this article in my blog for why you should always create a UNIQUE index on unique data:
P. S. There are some very, very special cases where you don't need a primary key.
Mostly they include log tables which don't have ANY indexes for performance reasons.
In short, no. However, you need to keep in mind that certain client access CRUD operations require it. For future proofing, I tend to always utilize primary keys.
Pretty much any time I've created a table without a primary key, thinking I wouldn't need one, I've ended up going back and adding one. I now create even my join tables with an auto-generated identity field that I use as the primary key.
I always have a primary key, even if in the beginning I don't have a purpose in mind yet for it. There have been a few times when I eventually need a PK in a table that doesn't have one and it's always more trouble to put it in later. I think there is more of an upside to always including one.
I know that in order to use certain features of the gridview in .NET, you need a primary key in order for the gridview to know which row needs updating/deleting. General practice should be to have a primary key or primary key cluster. I personally prefer the former.
Just add it, you will be sorry later when you didn't (selecting, deleting. linking, etc)
To make it future proof you really should. If you want to replicate it you'll need one. If you want to join it to another table your life (and that of the poor fools who have to maintain it next year) will be so much easier.
If you are using Hibernate its not possible to create an Entity without a primary key. This issues can create problem if you are working with an existing database which was created with plain sql/ddl scripts, and no primary key was added
Will you ever need to join this table to other tables? Do you need a way to uniquely identify a record? If the answer is yes, you need a primary key. Assume your data is something like a customer table that has the names of the people who are customers. There may be no natural key because you need the addresses, emails, phone numbers, etc. to determine if this Sally Smith is different from that Sally Smith and you will be storing that information in related tables as the person can have mulitple phones, addesses, emails, etc. Suppose Sally Smith marries John Jones and becomes Sally Jones. If you don't have an artifical key onthe table, when you update the name, you just changed 7 Sally Smiths to Sally Jones even though only one of them got married and changed her name. And of course in this case withouth an artificial key how do you know which Sally Smith lives in Chicago and which one lives in LA?
You say you have no natural key, therefore you don't have any combinations of field to make unique either, this makes the artficial key critical.
I have found anytime I don't have a natural key, an artifical key is an absolute must for maintaining data integrity. If you do have a natural key, you can use that as the key field instead. But personally unless the natural key is one field, I still prefer an artifical key and unique index on the natural key. You will regret it later if you don't put one in.
Except for a few very rare cases (possibly a many-to-many relationship table, or a table you temporarily use for bulk-loading huge amounts of data), I would go with the saying:
If it doesn't have a primary key, it's not a table!
Marc
Always best to have a primary key. This way it meets first normal form and allows you to continue along the database normalization path.
As stated by others, there are some reasons not to have a primary key, but most will not be harmed if there is a primary key
I am in the role of maintaining application created by offshore development team. Now I am having all kinds of issues in the application because original database schema did not contain PRIMARY KEYS on some tables. So please dont let other people suffer because of your poor design. It is always good idea to have primary keys on tables.
I know it is not common, but I normally do this: create table person_order(id int primary key, id_person int not null foreign key(person), id_order int not null foreign key(order), unique (id_person, id_order))
then, the joins like this: select ... from person_order_item poi join person_order po on po.id_person_order = poi.id
I am interested in your criticism
thanks
Does life have a primary key field? I've read in places that in the 1600's/1700's when the Gregorian calendar, and consequently the 24 hour clock, were introduced, that cyclic time lost a major foothold it once held. This is the big clencher behind most Mayan prophecies and lunar cycle cultures. But here's the thing we can all relate to... how do you distinguish this retro comeback from the last retro comeback? It's some continuous, always distinct counter, and that's the primary key field. Whether it's something relative to your data, or just a g#dda(|)n ID integer just-because, it's necessary in relational database land.