Logical vs Physical - Part 1 ' The Logical'

Two of the courses that we used to teach at Digital on RDB, where Logical Database Design and Physical Database Design. What I see now, over 25 years later is that very few people understand the distinction between logical and physical, mix them up and have no idea about basic relational theory.

The biggest area I see this occur is in the creation of Primary Keys.

The definition of a Primary Key is that the column/s are unique and not null.

Many people create PK's, but at a physical implementation level, wouldn't a unique constraint not allowing NULLs accomplish the same thing?

What makes a PK special?

The Foreign Key makes the Primary Key special, this is declarative referential integrity at the core, ensuring data integrity.

No use in having all of these PK's without corresponding FK's.

An easy example would be a customers table with a PK on customer id. This same customer id exists in the customers orders table with a FK constraint to the PK on the customers table. This means that the customer id in the customers table is unique and NOT NULL. Because of the PK/FK relationship you cannot insert a row in the customers orders table for a customer that doesn't exist in the customers table, so you cannot have orphan customer orders. Now you cannot delete a customer from the customers table if it has rows in the customers orders table.

This can also be applied to look up tables for countries, states, area codes etc. You can control what data is in your database.

By using PK's and FK's correctly you are ensuring some data integrity in your database, enforced by the database not by the application.