In my first data-heavy role, I inherited a database with no constraints. None. It was a free-for-all: users could have duplicate email addresses, orders could be logged for customer IDs that didn’t exist, and crucial fields like shipping addresses were frequently left empty. Reporting was a nightmare of constant data cleaning and guesswork.
I had always thought of constraints — PRIMARY KEY, FOREIGN KEY, and the like — as bureaucratic rules that got in the way of inserting data quickly. The breakthrough came when I stopped seeing them as restrictions and started seeing them as the fundamental architectural skeleton that gives a database its shape, reliability, and meaning. Without them, you don’t have a database; you have a spreadsheet filled with data chaos.
This tutorial ranks the four most essential constraints, not just by what they do, but by how they build on each other to create a genuinely trustworthy data structure from the ground up.
Ranking the 4 Core Constraints
We will build our understanding from the most basic rule to the most powerful relational concept. Each level solves a specific problem the one before it does not, culminating in a system where data integrity is not an accident, but a guarantee.
#4: The NOT NULL Constraint
This is the absolute baseline of data quality. A NOT NULL constraint on a column simply declares that it cannot be empty. It is your first and simplest defense against incomplete and useless records.
Imagine a users table where the email_address column is optional. How do you contact a user with a NULL email? You can’t. By making that column NOT NULL, you are enforcing a non-negotiable business rule at the database level: every user must have an email address.
This seems obvious, but failing to enforce it means every single query you write later has to include extra logic to handle the possibility of a missing email, creating complexity and potential bugs downstream. Applying NOT NULL is the first step toward making your data dependable by default.
#3: The UNIQUE Constraint
While NOT NULL prevents missing data, the UNIQUE constraint prevents duplicate data. A UNIQUE constraint on a column ensures that every single value in that column is distinct from every other value.
Continuing our users table example, applying a UNIQUE constraint to the email_address column guarantees you cannot have two different users sign up with the identical email. This prevents a whole class of logical problems, like “which user do we send this password reset email to?”
One critical distinction: a UNIQUE column can still contain NULL values (and in most systems, it can contain multiple NULLs, since NULL is not considered equal to another NULL). This is where our next constraint becomes essential.
#2: The PRIMARY KEY Constraint
The Primary Key is the undisputed centerpiece of table design. Conceptually, it is the perfect marriage of the NOT NULL and UNIQUE constraints. A column designated as a Primary Key must contain a unique value for every single row, and it cannot be empty, ever.
This is what creates a truly unambiguous identifier for each record. Columns like user_id, order_id, or product_sku are classic candidates for a Primary Key. It is the one value you can use to retrieve a specific row with absolute certainty that you have found the exact record you were looking for and not any other.
The mental model is simple: if a table tracks distinct items (customers, products, transactions), it needs a Primary Key to give each of those items a unique, permanent name. Without one, your table is just a collection of rows; with one, it is an indexed, addressable set of entities. Every genuinely important table you design should have a Primary Key. No exceptions.
#1: The FOREIGN KEY Constraint
If the Primary Key is the unique identity of a row in one table, the Foreign Key is the glue that connects it to another table, creating the entire “relational” aspect of a relational database. This is the most powerful concept of the four.
A Foreign Key is a column (or set of columns) in one table that refers directly to the Primary Key of another table.
This creates an unbreakable link and enforces what is called “referential integrity.” For example, if you have an orders table with a customer_id column, you would declare that column as a Foreign Key that points to the customer_id Primary Key in your customers table.
The database will now enforce this rule automatically:
- You cannot insert an order with a
customer_idthat does not already exist in thecustomerstable. This makes “orphaned” orders impossible. - You cannot delete a customer from the
customerstable if they still have active orders in theorderstable (unless you configure specific cascading rules).
This constraint single-handedly prevents the kind of data chaos I described in my introduction. It makes your data relationships explicit and guaranteed, rather than just an informal convention you hope everyone follows.
How Primary and Foreign Keys Work Together: A Real-World Blueprint
Let’s visualize the customers and orders relationship.
customers Table:
customer_id(PRIMARY KEY)customer_nameemail_address(UNIQUE)
orders Table:
order_id(PRIMARY KEY)order_dateorder_amountcustomer_id(FOREIGN KEY referencingcustomers.customer_id)
Here, the PRIMARY KEY of the customers table (customer_id) serves as the unique identifier for each customer. The FOREIGN KEY in the orders table (customer_id) does not identify an order, but instead points back to the customer who placed it. The database now ensures that any value you put in orders.customer_id must be a value that already exists in customers.customer_id. The relationship is locked in.
From Data Chaos to Data Confidence
That database I inherited taught me a hard lesson: data integrity is not an advanced feature, it is the foundation. Without the skeleton provided by constraints, a database will eventually collapse under the weight of its own inconsistencies.
Once I understood how these four constraints build upon one another — from simply requiring a value (NOT NULL), to requiring uniqueness (UNIQUE), to creating an ultimate identifier (PRIMARY KEY), and finally to linking those identifiers together (FOREIGN KEY) — I could design systems that were reliable by their very structure. The syntax became secondary to the architectural purpose it served.
Describe the two tables you’re trying to connect or the data rule you need to enforce. I can help you figure out which combination of PRIMARY KEY, FOREIGN KEY, and other constraints will build the reliable data structure you need.