Database Surrogate PKs – Few points

|
| By Webner

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!!

Free Online Certifications

Leave a Reply

Your email address will not be published. Required fields are marked *