Return to Answer

Autoincemental keys have mostly advantages.

But some possible drawbacks could be:

• If you have a business key, you have to add a unique index on that column(s) too in order to enforce business rules.
• When transfering data between two databases, especially when the data is in more than one table (i.e. master/detail), it's not straight-forward since sequences are not synced between databases, and you'll have to create an equivalence table first using the business key as a match to know which ID from the origin database corresponds with which ID in the target database. That shouldn't be a problem when transfering data from/to isolated tables, though.
• Many enterprises have ad-hoc, graphical, point-and-click, drag-and-drop reporting tools. Since autoincremental IDs are meaningless, this type of users will find it hard to make sense of the data outside of "the app".
• If you accidentally modify the business key, chances are you will never recover that row because you no longer have something for humans to identify it. That caused a fault in the BitCoin platform once.
• Some designers add an ID to a join table between two tables, when the PK should simply be composed of the two foreign IDs. Obviously if the join table is between three or more tables, then an autoincremental ID makes sense, but then you have to add an unique key when it applies on the combination of FKs to enforce business rules.

Here's a Wikipedia article section on the disadvantages of surrogate keys.

Autoincemental keys have mostly advantages.

But some possible drawbacks could be:

• If you have a business key, you have to add a unique index on that column(s) too in order to enforce business rules.
• When transfering data between two databases, especially when the data is in more than one table (i.e. master/detail), it's not straight-forward since sequences are not synced between databases, and you'll have to create an equivalence table first using the business key as a match to know which ID from the origin database corresponds with which ID in the target database. That shouldn't be a problem when transfering data from/to isolated tables, though.
• Many enterprises have ad-hoc, graphical, point-and-click, drag-and-drop reporting tools. Since autoincremental IDs are meaningless, this type of users will find it hard to make sense of the data outside "the app".
• If you accidentally modify the business key, chances are you will never recover that row because you no longer have something for humans to identify it. That caused a fault in the BitCoin platform once.
• Some designers add an ID to a join table between two tables, when the PK should simply be composed of the two foreign IDs. Obviously if the join table is between three or more tables, then an autoincremental ID makes sense, but then you have to add an unique key when it applies on the combination of FKs to enforce business rules.

Here's a Wikipedia article section on the disadvantages of surrogate keys.

Autoincemental keys have mostly advantages.

But some possible drawbacks could be:

• If you have a business key, you have to add a unique index on that column too in order to enforce business rules.
• When transfering data between two databases, especially when the data in more than one table (i.e. master/detail), it's not straight-forward since sequences are not synced between dbs, and you'll have to create an equivalence table first using the business key as a match to know which id from the origin db corresponds with which id in the target db. That shouldn't be a problem when transfering data from/to isolated tables, though.
• Many enterprises have ad-hoc, graphical, point-and-clic, drag-and-drop reporting tools. Since seq ids are meaningless, this types of users will find it hard to make sense of the data outside of "the app".
• If you accidentally modify the business key, chances are you will never recover that row because you no longer have something for humans to identify it. That caused a fault in the BitCoin plataform once.
• Some designers add an ID to a join table between two tables, when the PK should simply be composed of the two foreign IDs. Obviously if the join table is between three or more tables, then an autoincremental ID makes sense, but then you have to add an unique key when it applies on the combination of FKs to enforce business rules.

Here's a Wikipedia article section on the disadvantages of surrogate keys.

Autoincemental keys have mostly advantages.

But some possible drawbacks could be:

• If you have a business key, you have to add a unique index on that column(s) too in order to enforce business rules.
• When transfering data between two databases, especially when the data is in more than one table (i.e. master/detail), it's not straight-forward since sequences are not synced between databases, and you'll have to create an equivalence table first using the business key as a match to know which ID from the origin database corresponds with which ID in the target database. That shouldn't be a problem when transfering data from/to isolated tables, though.
• Many enterprises have ad-hoc, graphical, point-and-click, drag-and-drop reporting tools. Since autoincremental IDs are meaningless, this type of users will find it hard to make sense of the data outside of "the app".
• If you accidentally modify the business key, chances are you will never recover that row because you no longer have something for humans to identify it. That caused a fault in the BitCoin platform once.
• Some designers add an ID to a join table between two tables, when the PK should simply be composed of the two foreign IDs. Obviously if the join table is between three or more tables, then an autoincremental ID makes sense, but then you have to add an unique key when it applies on the combination of FKs to enforce business rules.

Here's a Wikipedia article section on the disadvantages of surrogate keys.

Autoincemental keys have mostly advantages.

But some possible drawbacks could be:

• If you have a business key, you have to add a unique index on that column too in order to enforce data duplication.
• When transfering data between two databases, especially when the data in more than one table (i.e. master/detail), it's not straight-forward since sequences are not synced between dbs, and you'll have to create an equivalence table first using the business key as a match to know which id from the origin db corresponds with which id in the target db. That shouldn't be a problem when transfering data from/to isolated tables, though.
• Many enterprises have ad-hoc, graphical, point-and-clic, drag-and-drop reporting tools. Since seq ids are meaningless, this types of users will find it hard to make sense of the data outside of "the app".
• If you accidentally modify the business key, chances are you will never recover that row because you no longer have something for humans to identify it. That caused a fault in the BitCoin plataform once.
• Some designers add an ID to a join table between two tables, when the PK should simply be composed of the two foreign IDs. Obviously if the join table is between three or more tables, then an autoincremental ID makes sense, but then you have to add an unique key when it applies on the combination of FKs to enforce business rules.

Here's a Wikipedia article section on the disadvantages of surrogate keys.

Autoincemental keys have mostly advantages.

But some possible drawbacks could be:

• If you have a business key, you have to add a unique index on that column too in order to enforce business rules.
• When transfering data between two databases, especially when the data in more than one table (i.e. master/detail), it's not straight-forward since sequences are not synced between dbs, and you'll have to create an equivalence table first using the business key as a match to know which id from the origin db corresponds with which id in the target db. That shouldn't be a problem when transfering data from/to isolated tables, though.
• Many enterprises have ad-hoc, graphical, point-and-clic, drag-and-drop reporting tools. Since seq ids are meaningless, this types of users will find it hard to make sense of the data outside of "the app".
• If you accidentally modify the business key, chances are you will never recover that row because you no longer have something for humans to identify it. That caused a fault in the BitCoin plataform once.
• Some designers add an ID to a join table between two tables, when the PK should simply be composed of the two foreign IDs. Obviously if the join table is between three or more tables, then an autoincremental ID makes sense, but then you have to add an unique key when it applies on the combination of FKs to enforce business rules.

Here's a Wikipedia article section on the disadvantages of surrogate keys.