9

If I don't need a primary key should I not add one to the database?

gbn
  • 422,506
  • 82
  • 585
  • 676
Luke101
  • 63,072
  • 85
  • 231
  • 359
  • 16
    There is hardly any case where you do not need a primary key. Basically, if a table doesn't have a primary key, it's not a table - it's just a heap of data. – marc_s Nov 20 '09 at 15:33
  • 2
    It may not be relevant to your question, but I do need to ask: *Why don't you feel you need one?* – Frank V Nov 20 '09 at 15:33
  • Can you give us more information about the situation you are dealing with? Primary keys are not always necessary but if we knew what you were trying to do we could maybe give you some better advice. – TLiebe Nov 20 '09 at 15:34
  • 2
    Can you give us an explanation as to why you think you might not need a pkey? i can't really think of a reason to ever omit it. Even a one table data model needs a pkey to uniquely ID a row. – Paul Sasik Nov 20 '09 at 15:34
  • People keep assuming that you need to uniquely identify a row... which I find interesting. Imagine some kind of log/counter - all I need to know is that something happened and when, to the nearest minute. So I have Event ID and Time Stamp and that's all I need because all I want to do is store and count event instances. Contrived I'll admit but not completely out there. – Murph Nov 20 '09 at 16:09
  • 2
    I have log tables like Murph suggests. When you have events that can be identical, but happen at the same time (especially if using SMALLDATETIME), what do you use for the primary key, and why? PKs are not necessary for performance (clustered indexes don't have to have anything to do with the PK) and if you also don't need to look anything up... In cases like this I use indexed views to provide aggregate stats but I see no reason to define a PK. I do agree though that in most cases, you DO want a primary key, and in most cases where you think you don't need one, you really do. – Aaron Bertrand Nov 20 '09 at 16:29

12 Answers12

42

You do need a primary key. You just don't know that yet.

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
  • 7
    I once thought I didn't need a primary key either... Now I know better. – MiseryIndex Nov 20 '09 at 16:07
  • 2
    +1 Because this most accurately reflects reality. I can see cases where one might not need one but few and far between. – Murph Nov 20 '09 at 16:10
  • 1
    Except for when you don't need a primary key, and you do know it now. This is an unusual case though. Better to play it safe and err in favor of a PK. – MarkPflug Nov 08 '10 at 19:20
  • 1
    I agree with your, but if someone doesn't agree with you and I, this argument never convinces them. – davidtbernal Jan 19 '11 at 22:05
13

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
  • 422,506
  • 82
  • 585
  • 676
3

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
  • 97,670
  • 29
  • 122
  • 130
2

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
  • 49,320
  • 14
  • 110
  • 160
  • 5
    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. – Reverend Gonzo Nov 20 '09 at 16:11
  • @Rev Gonzo, what you need is a more strategically placed beer holder. C'mon it's 2010. – JeffO Mar 08 '10 at 17:56
2

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.

JeffO
  • 7,957
  • 3
  • 44
  • 53
1

Yes, a table should ALWAYS have a primary key... unless you don't need to uniquely identify the records in it. (I like to make absolute statements and immediately contradict them)

When would you not need to uniquely identify the records in a table? Almost never. I have done this before though for things like audit log tables. Data that won't be updated or deleted, and wont be constrained in any way. Essentially structured logging.

MarkPflug
  • 28,292
  • 8
  • 46
  • 54
0

That depends very much on how sure you can be that you don't need one. If you have just the slightest bit of doubt, add one - you'll thank yourself later. An indicator being if the data you store could be related to other data in your DB at one point.

One use case I can think of is a logging kind-of table, in which you simply dump one entry after the other (to properly process them later). You probably won't need a primary key there, if you're storing enough data to filter out the relevant messages (like a date). Of course, it's questionable to use a RDBMS for this.

DanMan
  • 11,323
  • 4
  • 40
  • 61
0

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.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • 1
    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 Nov 20 '09 at 15:32
  • 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. – Adriaan Stander Nov 20 '09 at 15:35
  • 1
    Luke101 should post his use case and we will see, if he needs one. – brainfck Nov 20 '09 at 15:37
0

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
  • 638
  • 6
  • 13
  • 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 Nov 20 '09 at 20:12
  • 3
    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 Mar 04 '10 at 15:46
0

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
  • 771
  • 2
  • 9
  • 13
0

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
  • 3,197
  • 18
  • 15
0

You should always have a primary key, even if it's just on ID. Maybe NoSQL is what you're after instead (just asking)?

jschorr
  • 3,034
  • 1
  • 17
  • 20