BEST PRACTICE: SQL Composite or Auto-generated Primary Key

There are lots of arguments whether to use Composite Primary Key or Auto-generated/Unique Primary Key.

There are PROs & CONs for both of these techniques.

But if you will have multiple child tables and you will have multiple foreign key relationships, only use auto-generated key approach.

Why? If you use composite key with 3 columns, when you join the two tables, you need to compare all these 3 columns in join. So if you need to join multiple tables using composite key consisting 2 or more columns, can you imaging how your SQL becomes too complex. However, if you use auto-generated key, you only need to compare 1 column.

But do remember to assign Unique Constraints if you use auto-generated key.

Consider this:

PK	ProductID	Color	Price
1	1001	Red	$100
2	1001	Blue	$200

Product ID and Color is the row identifier for the table. Later you mistakenly insert ProdcutID:1001 and Color:Red. Then your data integrity is lost.


To avoid, you must add Unique Constraints for combination of ProductID and Color.


Hope worth reading.