Primary and Surrogate Keys
Explore the concepts of primary and surrogate keys in PostgreSQL to enforce data uniqueness and support normalization principles. Understand how primary keys prevent null values and duplicates, and how surrogate keys serve as artificial substitutes. The lesson guides you through schema fixes and unique constraints to maintain data integrity and references between tables.
We'll cover the following...
Primary key
Primary keys are a database constraint allowing us to implement the first and second normal forms. The first rule to follow to reach the 1NF says, “there are no duplicated rows in the table.”
A primary key ensures two things:
-
The attributes that are part of the primary key constraint definition are not allowed to be
null. -
The attributes that are part of the primary key are unique in the table’s content.
To ensure that there is no duplicated row, we need the two guarantees. Comparing null values in SQL is a complex matter—as seen in Three-Valued Logic chapter—and rather than argue if the no-duplicate rule applies to null = null (which is null) or to null is not null (which is false), a ...