Should you ever not assign an unique ID to each row in a table?

Question

Possible Duplicate:
Should every table have a single-field surrogate/artificial primary key?

I can't imagine ever not using a unique ID with every table, which is why I'm asking this question. Should you ever not assign an unique ID to each row in a table?

score 22 · Answer 1 · answered Sep 22 '11 at 14:31

Yes, for example in case of a mapping table representing an N:M association between two tables. In its simplest form, it contains only two foreign keys, and these form its compound key, so it needs no separate ID column. In fact, adding an ID to such a table (without a uniqueness constraint, as pointed out by @Jeff) would allow one to add multiple associations between the same two rows, which is usually not desired.

score 7 · Answer 2 · answered Sep 22 '11 at 14:52

Here is a situation I'm on the fence about and that is many-to-many "relationship".

Table: Students (PK_StudentID)
Table: Teachers (PK_TeacherID)
Table: StudentsTeachers(PK_StudentsTeachersID, FK_StudentID, FK_TeacherID), Unique Contraint on (FK_StudentID, FK_TeacherID)

*Let's not let semester or school year cloud the issue.

Does the StudentsTeachers table really need the PK_StudentsTeachersID? All of these id's have no intelligence added to them. They're just unique numbers. Is there a need to create any part of the student record without indicating the teacher? If you need to join this table to a StudentTeacherMeeting table, having the one field for a join is always nice, but then you require the join just to get a list of those meetings for a particular teacher.

Speed, select flexibility, join compliations, memory, disk space, you just factor this stuff on a case by case basis.

score 6 · Answer 3 · answered Sep 22 '11 at 14:58

You need the key when you want to:

update/delete/read specific row of data
if the table has 1 or more child tables
to enforce uniqueness by implementing a business rule (e.g. customer number must be unique)

Examples where you don't need a key field:

Fact tables in the data warehouse can be defined without keys. A detailed description may be found here: To Key or Not To Key
Log tables don't require a column for an identifier.
In ETL applications its common to load data into staging area without ID and process the input data sequentially, the drop the entire table.
If your table will only ever contain 1 row (very rare!)
If your table has a natural key that is guaranteed (somehow) to be correct and the amount of information is small and you are using this data for read only. In such a case, the key will not add value

score 4 · Answer 4 · answered Sep 22 '11 at 15:01

Every table should have a Primary key. It could be a single column field that is an int a decimal, a varachr, etc. or it could consist of multiple columns that are unique in combination. It should be something that will not change frequently. But it is critical to be able to uniquely identify a record in every table.

It should not be a varchar(max) or nvarchar(max) or a bit field. If it is a GUID there are special considerations. A long varchar or nvarchar field could cause performance issues especially if the field is subject to change like a company name and there are chile tables that contain this field as well.

Some people believe strongly in natural keys and thus do not use surrogate keys, others believe surrogate keys are the best way. There are arguments both in favor and against both approaches.

score 3 · Answer 5 · answered Sep 22 '11 at 14:32

I have a table that contains associations between one thing and another whole set of things. Think of the categories that a product might be in. Could be in one, could be in thirty. And when you post the editor page, the easiest thing to do is to delete all the records for the currently-being-edited product and insert new records for the just-submitted values.

If you have an incrementing ID field, not only is it useless (when are you ever going to query that table by it's... what, association id number??), but it grows super fast and ends up overflowing its field.

Best to make a unique key between the product and category IDs, and not worry about an incrementing primary key.

score 1 · Answer 6 · answered Sep 22 '11 at 14:26

There're such cases. In Domain Driven Design we have things called "Value Objects" which don't need an ID. These are objects with naturally posess no identity at all and thus you needn't store one for them. It's also considered a best practice to keep them immutable.

But before you create tables which store the same value object multiple times think about normalization. Often times it's better to have a key (and be it surrogate) and just store the amount of items you have. Think about your design very well before you decide to leave out a PK.

So just because you don't necessarily need them does not always mean you should not have them. PKs make life oh so much easier.

score 0 · Answer 7 · answered Sep 22 '11 at 14:33

0

Logical keys have their place but the architecture of the overall system needs to be determined to decide this. Many ORM mapper tools fail on tables without a unique (EDIT: single column) primary key. (Eg. EntityFramework)

answered Sep 22 '11 at 14:33

maple_shaft

273
2
5
15

score 0 · Answer 8 · answered Sep 22 '11 at 15:05

One possible situation where I wouldn't use a unique Id would be a logging table. This tables usually do not require having one, nor a PK for that matter. (Unless using them with ORMs that might cause trouble for lacking PK).

However, personally I always use a unique RowId independent from the table's PK, unless its PK is the unique Id (Except on the aforementioned case). This is because it makes change logging easier as PK can change as well.

Should you ever not assign an unique ID to each row in a table?

8 Answers8