CODEWITHSUNDEEP
sql-serverdatabasedatabase-design
Majiy
Majiy

Reputation: 1920

Why would primary keys be stored in another table, instead of using auto-increment?

In a third party software that uses a relational database (mssql, but this question is not limited to this specific database) I have seen the following construct:

Several tables have integer primary keys, which are not auto-increment. Instead, the currently highest primary-key (per table) is stored inside another table. That table contains only two columns: tableName und currentPrimaryKey.

Whenever a new row is inserted in one of the tables, a stored procedure is used to lock the primary-key-table, obtain the next-primary-key-to-be-used, und unlock the primary-key-table again.

My question is: Are there any advantages this construct offers, compared to simply using an auto-increment primary key?

Upvotes: 2

Views: 205

Answers (3)

nvogel
nvogel

Reputation: 25526

Software vendors usually want their products to support several DBMSs and different versions of DBMSs. MySQL has no built-in table-independent sequence generator. SQL Server also lacked such a feature until SQL Server 2012. A sequence generator based on a dedicated table is a common work-around for implementing such sequences without relying on any special DBMS feature.

Perhaps the software vendor also wanted to avoid different versions of the column-bound "auto increment" sequence generator. SQL Server for example doesn't allow updates to auto-incrementing columns (aka IDENTITY columns) whereas MySQL does allow updates. If the software vendor relied only on the built in features in each DBMS then their product would possibly behave differently on different DBMSs.

Upvotes: 1

Tanner
Tanner

Reputation: 22733

Are there any advantages this construct offers, compared to simply using an auto-increment primary key?

In most scenarios, no, it just sounds like an archaic mechanism to get a unique Id. Use the built in tools to do the job they are designed for.

There may be scenarios where you have multiple tables or databases where you want to keep the keys unique across tables/databases for some business rules. But if it's just a single table then I'd stick with the built in auto increment.

Whenever a new row is inserted in one of the tables, a stored procedure is used to lock the primary-key-table, obtain the next-primary-key-to-be-used, and unlock the primary-key-table again.

With this, depending on the traffic of your system, it could lead to delays as the lookup table gets locked when it needs to insert a new row in any of the tables involved. The delays may be insignificant amounts of time, but the higher the traffic the higher the delays will become.

Plus this is all code somebody has to manage and maintain, where as the built in mechanisms are managed for you.

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

It will guarantee sequential assignment with no gaps, which IDENTITY doesn't guarantee, and may be required under certain legal/regulatory regimes.

At the cost of serializing all inserts, which is a high cost to pay if the lack of gaps isn't a requirement. (IDENTITY instead acts "outside" of any transaction and so if a transaction is rolled back, that particular identity value is unused)

Upvotes: 5

Related Questions