I am sure this is obvious stuff but anybody can overlook it, so I thought of adding it in my blog. Many programmers and database designers are using surrogate keys instead of natural keys as PKs (primary keys) now a days. Numeric surrogate keys, obviously are easier and better performance wise especially in cases where composite keys are required. At the same time, just adding a numeric PK is not enough, more constraints are required to enforce data integrity.
Here’s an example:
Suppose I have a table PRODUCT with fields: product_id vendor_id product_name date_of_manufacturing date_of_expiry
Assume that PK is (product_id,vendor_id)
Now I decide to add a single column numeric surrogate key:
id NUMBER PK product_id vendor_id product_name date_of_manufacturing date_of_expiry
This means all constraints that earlier PK enforced upon fields product_id and vendor_id are gone, so I need to EXPLICITLY define those to keep data in right spirits. Specifically:
* Add NOT NULL constraints for product_id & vendor_id
* Add UNIQUE constraints for product_id & vendor_id
Once we do above, we are fine!!