If I don't need a primary key should I not add one to the database?
+3
A:
If you don't need a primary key then don't use one. I usually have the need for primary keys, so I usually use them. If you have related tables you probably want primary and foreign keys.
Bob
2009-11-20 15:31:21
+1 short and sweet :)
James
2009-11-20 15:33:00
A:
A primary key will always help with query performance. So if you ever need to query using the "key" to a "foreign key", or used as lookup then yes, craete a foreign key.
astander
2009-11-20 15:31:48
I don't get the fact, that primary keys help with performance? If you don't need them, you won't look up records by using one. So, there is no benefit.
brainfck
2009-11-20 15:32:58
What will the table be used for then? mostly we need the table to store some information, and you will be looking up values from it.
astander
2009-11-20 15:35:19
+2
A:
Yes, but only in the same sense that it's okay not to use a seatbelt if you're not planning to be in an accident. That is, it's a small price to pay for a big benefit when you need it, and even if you think you don't need it odds are you will in the future. The difference is you're a lot more likely to need a primary key than to get in a car accident.
You should also know that some database systems create a primary key for you if you don't, so you're not saving that much in terms of what's going on in the engine.
Larry Lustig
2009-11-20 15:36:58
I disagree. I refuse to wear seatbelts as they prevent me full access to my beer.But I will never create a table without a pk.
Milan Ramaiya
2009-11-20 16:11:29
@Rev Gonzo, what you need is a more strategically placed beer holder. C'mon it's 2010.
Jeff O
2010-03-08 17:56:54
I once thought I didn't need a primary key either... Now I know better.
MiseryIndex
2009-11-20 16:07:10
+1 Because this most accurately reflects reality. I can see cases where one might not need one but few and far between.
Murph
2009-11-20 16:10:45
+11
A:
A primary key uniquely identifies a row in your table.
The fact it's indexed and/or clustered is a physical implementation issue and unrelated to the logical design.
You need one for the table to make sense.
gbn
2009-11-20 15:38:35
A:
I don't know. I have used a couple tables where there is just a single row and a single column. Will always only be a single row and a single column. There is no foreign key relationships.
Why would I put a primary key on that?
ElGringoGrande
2009-11-20 15:47:26
You need to store a single piece of information? Something like a system wide preference? Everybody needs that piece of data but you don't want to create a whole new thing just to get at that one piece of data.
ElGringoGrande
2009-11-20 20:12:22
Ah. But you do want a primary key on this table. And a check constraint that limits that primary key to a single value. Because otherwise you come back in 6 months time and find that someone else has added another row for you, and which one your application is picking up is arbitrary...
Damien_The_Unbeliever
2010-03-04 15:46:54
A:
A primary key is mainly formally defined to aid referencial Integrity, however if the table is very small, or is unlikely to contain unique data then it's an un-necessary overhead. Defining indexes on the table can normally be used to imply a primary key without formally declaring one. However you should consider that defining the Primary key can be useful for Developers and Schema generation or SQL Dev tools, as having the meta data helps understanding, and some tools rely on this to correctly define the Primary/foreign key relationships in the model.
Wiretap
2009-11-20 15:48:36
A:
Well...
Each table in a relational DB needs a primary key. As already noted, a primary key is data that identies a record uniquely...
You might get away with not having an "ID" field, if you have a N-M table that joins 2 different tables, but you can uniquely identifiy the record by the values from both columns you join. (Composite primary key)
Having a table without an primary key is against the first normal form, and has nothing to do in a relational DB
Heiko Hatzfeld
2009-11-20 16:01:55
A:
No, unless you can find an example of, "This database would work so much better if table_x didn't have a primary key."
You can make an arguement to never use a primary key, if performance, data integrity, and normalization are not required. Security and backup/restore capabilities may not be needed, but eventually, you put on your big-boy pants and join the real world of database implementation.
Jeff O
2010-03-08 18:07:07