14

Is it an acceptable practice to use a single sequence as a primary key across all tables (instead of a primary key being unique for a given table, it is unique for all tables)? If so, is it objectively better than using a single primary key sequence across tables.

I'm a junior software developer, not a DBA, so I am still learning many of the basics of good database design.

Edit: In case anyone is wondering, I recently read a critique of a database design by one of our company's DBAs who mentioned it was a problem that the design didn't use a single primary key across the entire database, which sounded different than what I've learned so far.

Edit2: To answer a question in the comments, this is for Oracle 11g, but I was wondering on a non-database specific level. If this question does depend upon the database, I would be interested to know why, but in such a case I would be looking for an answer specific to Oracle.

|improve this question
  • 2
    It's usually a terrible idea, for performance reasons. – Philᵀᴹ Sep 27 '13 at 13:47
  • 1
    Actually there is a stronger benefit in having each table with its own, independent primary key range. But only in that when you look at a bunch of IDs you could say, this one is Accounts, that one is PurchaseHeader, etc. Doing this requires some setup and (like any special purpose thing) some ongoing care and feeding. (Yes, I have worked with a system like this, many years ago.) – RLF Sep 27 '13 at 15:23
  • Which DBMS are you using? Oracle? Postgres? DB2? – a_horse_with_no_name Sep 27 '13 at 16:09
  • 1
    Is it possible you misinterpreted what he meant? Maybe he wasn't being that literal? – JamesRyan Sep 27 '13 at 16:21
  • Did the company DBA actually mean there are no primary key fields present in any of the tables? – Max Vernon Sep 27 '13 at 16:46
13

Acceptable? Sure. Common? No. Beneficial? Doubtful.

At my old job we inherited a system where they had a central sequence generator (this was a SQL Server system long before SEQUENCE was introduced in SQL Server 2012). It wasn't really a performance bottleneck and shouldn't be unless you're generating hundreds of thousands of values per second. But it made all of the code a lot more complex than it had to be, for no good reason. The intent of the design was to be sure that if something in the system was assigned an ID value of 12, only one thing in the system could have the ID 12. This seemed quite obtuse to me and I never understood it. If I have a customer with CustomerID = 12, why does that preclude me from having an order with OrderID = 12?

I do see the usefulness of a central sequence generator if you have multiple systems and you are generating IDs for a certain type of entity (say, a customer or an order) from these multiple systems. A central sequence can dole out new values to multiple systems without being a bottleneck (just a single point of failure) and without fear of two systems generating the same ID.

|improve this answer
  • If you had to choose between something like this and just using uniqueidentifiers as primary keys, would you have a preference (though the answer is likely "it depends")? It seems like a GUID would work around the problem the same way, except that you'd get a standard implementation rather than having to roll your own centralized primary-key-generator. Obviously, using a sequence in SQL 2012 would accomplish both things, but assuming somebody is on an older version? – SqlRyan Sep 27 '13 at 15:49
  • 2
    @SqlRyan I'd need to understand why an OrderID has to be completely distinct from a CustomerID. I almost certainly wouldn't use a GUID for this; setting up IDENTITY ranges might be better (Customers starts at 1, Orders start at 1000000, etc.) with alerts in place for when you've come close to exhausting the range of course. – Aaron Bertrand♦ Sep 27 '13 at 15:56
  • 1
    @SqlRyan - using a poorly implemented GUID as the clustered primary key can cause all sorts of problems. As Aaron said, IDENTITY fits the purpose far better. – Max Vernon Sep 27 '13 at 16:57
  • In a previous system I have seen using a single sequence across the entire database, this was done to allow for a foreign key to point to numerous different tables instead of a single table, so that when you said that the foreign key of two different rows were 12, you knew they pointed to the same thing without needing to check what possible table they pointed to. A 13 in the same column could potentially be the primary key on a different table. I am personally very uncomfortable with that design style. – Lawtonfogle Sep 27 '13 at 17:05
  • @AaronBertrand Or alternatively use simple integer identifiers and append some code to the beginning when these are customer facing. eg. I1337, C1337 clearly an invoice or customer – JamesRyan Sep 27 '13 at 20:32
7

The idea has merit in a very complex database where people could accidentally join to a table using the wrong column and get invalid rows just because the INT IDs are the same.

We chose to have sequential GUIDs as our primary keys in order to avoid some of the Index fragmentation pitfalls of GUIDs. Sadly they are quite large.

SQL server can generate sequential GUIDs via a default invoking the newSequentialID() function, so there no table of issued keys to maintain and no blocking bottleneck.

This has given us unique IDs across the whole databases, across our entire enterprise actually as they are truly unique.

The price of course is space and its problematic when your trying to bring the data across to a Data Warehouse / Cube where speed/size is predicated on using smaller Integer keys.

I am convinced we have avoided many bugs in our app as a result of using them.

|improve this answer
4

I can't imagine what might be the reason behind the single sequence across all tables. All it does is create a bottleneck when generating new values.

No matter how small is the overhead of generating sequential key values, the generator is a single resource, access to which must be synchronized. The more requests it gets, the higher the chances that some requesters will have to wait for their turn at the tap. It is obvious that the single sequence generator shared between all tables will be accessed more frequently by more clients, thus producing more contention, than any one of multiple generators. The contention may become more pronounced if business rules impose constraints upon the generated values, such as the absence of gaps or strict ordering, or in a clustered database.

Even with the most efficient sequence generator there will be a workload that causes untolerable contention.

|improve this answer
  • 2
    You might want to add details about how the bottleneck is created and why that is a bad idea. – Max Vernon Sep 27 '13 at 16:54
2

the purpose of PrimaryKey in Database tables is primarily to enforce the uniqueness of data that supposed to be unique, because all the workflows can not be covered and assured that it will not result in duplication of data. Second reason is, many times PK is also primary candidate for clustered Index on table so it also boost the retrieval of data when/where these columns are properly used in select query.

using a sequence number as Primary key is same as every table has Identity column and only that column is being used in PrimaryKey. having single sequence number across the DB must have some specific usage but from the PrimaryKey point of view I do not understand reason. for example in one of the Datawarehouse project I worked on, we have Column called LoadBatchID and from ETL to reporting 50% of all table has this column but at some places it has different meaning. we used the unique proc as number generator to make sure we do not find conflicts and also help us tracing back to the original file from where data came from and what happen at each different stages of ETL.

|improve this answer
2

I suppose one reason to do it would be if all entities inherited from some parent entity. Say for example you wanted to be able to put a comment on any type of entity:

create table god_entity (
  id bigserial primary key
);

create table some_table (
  id bigint primary key references god_entity(id),
  ...
);

create table some_other_table (
  id bigint primary key references god_entity(id),
  ...
);

create table comment (
  id bigint primary key references god_entity(id),
  ...
);

create table entity_comment (
  entity_id bigint not null references god_entity(id),
  comment_id bigint not null references god_entity(id),

  primary key (entity_id, comment_id)
);

Usually this is not done. .

Don't know about the performance characteristics.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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