Database Constraints

Constraints are rules enforced by the DBMS to maintain data integrity and consistency. They are a core part of the Relational Model and are checked automatically on every INSERT, UPDATE, and DELETE.

Keys

There are three kinds of Keys

• PRIMARY - generally used to auto increment a field
  1. Never use LOGICAL keys as the primary key
  2. LOGICAL keys can and do change, albeit slowly, such as username and email_id
  3. Relationships that are based on string matching are less efficient and are slower than those based on integer
• LOGICAL - what the application/outside world uses for lookup such as username, email-id
• FOREIGN - generally an integer pointing to a row in another table, it points to a Primary Key in another table
  1. Always have the attribute or the column that is a pointing to another table in the following format _<table_name>id this enables the reader to understand which table the foreign key is pointing to

See Entity-Relationship Diagram (ERD) for the full key taxonomy: Primary Key, Foreign Key, Composite Key, Candidate Key, Alternate Key, Surrogate Key, Superkey.

PRIMARY KEY can be easily created with the SERIAL keyword in the column which one would want to have be made as a primary key such as

CREATE TABLE users(
id SERIAL,
name VARCHAR(128),
email VARCHAR(128) UNIQUE,
PRIMARY KEY(id),
);

in the above command the column id will be created a PRIMARY KEY and an INDEX is automatically created on this column.

The UNIQUE constraint is a logical key which is used to make sure they are no duplicates in the column, this column is also auto-indexed.

Constraint Types

Constraint	Purpose	Example
PRIMARY KEY	Uniquely identifies each row	id SERIAL PRIMARY KEY
FOREIGN KEY	References a Primary Key in another table	REFERENCES users(id)
UNIQUE	No duplicate values in column	email VARCHAR(255) UNIQUE
NOT NULL	Column cannot be empty	name VARCHAR(128) NOT NULL
CHECK	Value must satisfy a boolean expression	CHECK (age >= 0)
DEFAULT	Fallback value if none provided	status VARCHAR(20) DEFAULT 'active'
EXCLUDE	Prevents overlapping ranges (PostgreSQL)	EXCLUDE USING gist (room WITH =, period WITH &&)

On Delete Constraint

We use this constraint when we’d like to avoid zombie data or zombie data links, this directs what should happen to the child data when the parent data is deleted.

There are four options:

• Default/RESTRICT - Don’t allow changes that break the constraint.
• CASCADE - Adjust child rows by removing or updating to maintain consistency
• SET NULL - Set the foreign key columns in the child to null when the parent data is deleted
• SET DEFAULT - Set the foreign key columns to their default value

Similarly, ON UPDATE supports the same actions when the referenced Primary Key changes.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Domain Constraints

Beyond column-level constraints, Normalization and proper schema design prevent data anomalies. Functional Dependencies determine which constraints are needed to reach a desired normal form.