CODEWITHSUNDEEP
sqlauto-incrementcomposite-primary-key
Igor_K
Igor_K

Reputation: 39

Table design with composite key requirement

I am building the database for storing client-specific configurations. Each config shall have a unique ID(int) and a version(int). Only the pair of them shall be unique, which pushes me into creation of a composite key. However, the problem is that ID may be auto-generated, while version shall always start at 1 and be incremented each time the change to the configuration is made.

Most of the posts here are not recommending neither to use composite keys, nor to have a composite key with SQL auto-increment. One of the recommendations from my colleagues is to add a separate ID as a single Primary key and manage configID+Version pair assignment and uniqueness by myself in application code.

So, at the end, I see 3 options:

  1. PK(configID,version) with manual generation of both

  2. PK (configID,version) with auto-increment config ID and manual assignment of version

  3. PK (ID) auto-increment with configID+version pair managed by the code.

Can you please advise what is the best option? Maybe something else?

Upvotes: 0

Views: 306

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You should choose the third option, an auto-incremented id.

Why?

The primary key serves several purposes. Perhaps the most important is that it uniquely identifies each row in a table for foreign key references. A single column foreign key is much simpler than a composite foreign key -- both in terms of defining the key and in terms of performance. Two columns generally takes up more space than 1.

This also has some nice benefits. This allows you to change the config or version associated with each pair. You may not want to do this now. But in the future, you might say: "Gosh, I wish I could easily delete a version". Or, "These two clients merged and I want to bring together their configurations."

It might also make sense to have the configuration be auto-incremented. I'm not sure about that. To assign the version, you'll need a trigger to maintain the sequential number for each config.

Upvotes: 1

Related Questions