Sharing a single primary key sequence across a database?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.