6

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?

Improve this question
8
  • 2
    Do you mean a unique key/constraint like a PK or do you mean a single identity column by unique ID?
    – Falcon
    Sep 22, 2011 at 14:38
  • I agree with falcon: It's not entirely clear what you're asking. Maybe you could give an example?
    – FrustratedWithFormsDesigner
    Sep 22, 2011 at 14:55
  • For the record, I'm not ENTIRELY happy with this question.
    – jcolebrand ♦
    Sep 22, 2011 at 23:13
  • @jcolebrand how about we fix it to fit the answers? Several including myself have taken the question to be about surrogate keys.
    – Jack Douglas
    Sep 23, 2011 at 5:43
  • @JackDouglas I am all for that, since the OP hasn't shown up in 22 hours ... however, we need to document that we did that. Got any ideas?
    – jcolebrand ♦
    Sep 23, 2011 at 14:26

8 Answers 8

Reset to default
22

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.

Improve this answer
4
  • 4
    You can have a unique contraint outside of the unique id.
    – Jeff O
    Sep 22, 2011 at 14:39
  • 2
    @Jeff, OK, that may be needed sometimes (e.g. if your ORM can't deal with a compound ID in a specific situation). Otherwise, it is a question of personal preference - I prefer simplicity, but I know there are people who prefer having an ID in each table. (And they may even have good reasons I am not aware of - I am not a DB guru.)
    – Péter Török
    Sep 22, 2011 at 14:54
  • @peter I can't speak for all RDBMs, but for instance, InnoDB for MySQL stores the primary key in all other indexes for lookup. So a combined primary key can end up taking a lot of space if the table also has other indexes.
    – Derek Downey
    Sep 22, 2011 at 17:20
  • @DTest, I belive that some other DBMS instead just store a pointer to full record in the heap or main index. That pointer can even potentially be joined against another index without ever fetching the real primary key, although I'm not aware if any DBMS bothers to do that.
    – Kevin Cathcart
    Sep 23, 2011 at 15:59
7

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.

Improve this answer
0
6

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

Improve this answer
5
  • 1
    I always add a surrogate key to my ETL staging tables, it makes it so much easier to identify dups when I am looking at the intial file to see what transforms I need to build.
    – HLGEM
    Sep 22, 2011 at 15:00
  • 1
    Actually, it's very useful for log tables to have some kind of sequence number. Even if you track the time of log messages down to the second, you can have multiple messages per second. And when debugging, it's useful to know which of those statements came first. Using an increasing ID as a sequence number can help with this.
    – FrustratedWithFormsDesigner
    Sep 22, 2011 at 15:07
  • @HLGEM, I have added a link to the text where different opinions are debated in the case of fact tables. Your approach is valid, however, in many cases, fact tables don't have a meaningful business key and the key is enforced on the row by ETL process. In such cases, to gain performance for 10s of millions of rows, you may decide to not have a key.
    – Emmad Kareem
    Sep 22, 2011 at 15:19
  • @FrustratedWithFormsDesigner, you are correct. If you need to track exact sequence, an auto-generated ID would be required.
    – Emmad Kareem
    Sep 22, 2011 at 15:21
  • @Emmad Kareem, interesting link. Data warehouses are a special case all around I think and I wasn't thinking of them in my comment as we use ETL to load to OLTP databases.
    – HLGEM
    Sep 22, 2011 at 15:28
4

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.

Improve this answer
3

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.

Improve this answer
4
  • 1
    He wasn't asking about auto-incrementing primary keys but unique primary keys in general, and even if that was the discussion I still disagree with you on principle.
    – maple_shaft
    Sep 22, 2011 at 14:35
  • 1
    To a user, they may never search on this id, but once you have isolated a record and want to update it in the db, having a single, no questions asked unique identifier (auto created or not), is a great thing.
    – Jeff O
    Sep 22, 2011 at 14:42
  • I'm for having a unique key. That should be clear from my answer. In the case of a N:M table, that unique key should be composed from the foreign key fields. That's all I'm saying.
    – Dan Ray
    Sep 22, 2011 at 15:20
  • 2
    Strange that this answer is at -2 currently when it says the same thing the top answer at +7 says...
    – Dan Ray
    Sep 22, 2011 at 15:21
1

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.

Improve this answer
2
  • Whether or not you have a primary key in the table is not the quesion. It's should it always be a unique identifier.
    – Jeff O
    Sep 22, 2011 at 14:54
  • 1
    @Jeff O: Honestly, we don't know what he is asking about yet.
    – Falcon
    Sep 22, 2011 at 14:55
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)

Improve this answer
1
  • 4
    I primary key does not have to be a single id field, but could be a compound key.
    – Jeff O
    Sep 22, 2011 at 14:40
0

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.