Chapter 14 of 35

SQL Constraints

Over the last few chapters MySQL kept refusing things: an employee without a name, a duplicate email, an order for a customer that does not exist, deleting a department that still has staff. None of that was MySQL being difficult. Those were constraints doing their job.

Applications have bugs, people make typing mistakes, and two programs may write to the same table at the same time. If the only thing protecting your data is application code, bad data will get in sooner or later. Constraints put the rules inside the database itself, so every INSERT and UPDATE from every program is checked, every time.

In this chapter we will study each constraint MySQL supports (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT), see real violations and error messages, learn the cascade rules for foreign keys, and add and remove constraints on existing tables. Outputs are real MySQL 8.4 output, run against a freshly loaded company_db.


What is a Constraint?

A constraint is a rule attached to a table or column that the database enforces automatically. Any INSERT, UPDATE or DELETE that would break the rule is rejected, and the data stays unchanged.

Constraints protect data integrity, which comes in three flavours you will also meet in DBMS theory:

  • Domain integrity: each value is valid for its column (NOT NULL, CHECK, data types, DEFAULT).

  • Entity integrity: each row is uniquely identifiable (PRIMARY KEY, UNIQUE).

  • Referential integrity: references between tables point to rows that exist (FOREIGN KEY).

You can list the constraints of a database from MySQL's catalogue tables. Here are the ones in company_db:

SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'company_db'
ORDER BY TABLE_NAME, CONSTRAINT_TYPE;
+------------------+-------------+-----------------+
| CONSTRAINT_NAME  | TABLE_NAME  | CONSTRAINT_TYPE |
+------------------+-------------+-----------------+
| PRIMARY          | customers   | PRIMARY KEY     |
| PRIMARY          | departments | PRIMARY KEY     |
| dept_name        | departments | UNIQUE          |
| employees_ibfk_1 | employees   | FOREIGN KEY     |
| employees_ibfk_2 | employees   | FOREIGN KEY     |
| PRIMARY          | employees   | PRIMARY KEY     |
| email            | employees   | UNIQUE          |
| orders_ibfk_1    | orders      | FOREIGN KEY     |
| PRIMARY          | orders      | PRIMARY KEY     |
+------------------+-------------+-----------------+
9 rows in set (0.00 sec)

Notice names like employees_ibfk_1. We never named those foreign keys, so MySQL generated names. We will see shortly why giving your own names is a good habit. (NOT NULL and DEFAULT are column properties in MySQL, so they do not appear in this list.)

Column-Level vs Table-Level Syntax

A constraint can be written right next to a column, or as a separate line in the table definition:

-- column-level: part of the column definition
email VARCHAR(100) UNIQUE

-- table-level: its own line, can cover several columns, can be named
CONSTRAINT uq_emp_email UNIQUE (email)

Both mean the same thing for a single column. Table-level syntax is required when a constraint covers more than one column (a composite key), and it lets you choose the name with CONSTRAINT name. NOT NULL and DEFAULT can only be written at column level.


1. NOT NULL

NOT NULL means the column must always have a value. NULL is rejected.

In employees, name, salary and hire_date are NOT NULL. Try to break it:

INSERT INTO employees (emp_id, name, salary, hire_date)
VALUES (11, NULL, 50000, '2024-06-01');
ERROR 1048 (23000): Column 'name' cannot be null

Leaving the column out entirely does not help, because then MySQL needs a default value, and salary has none:

INSERT INTO employees (emp_id, name, hire_date)
VALUES (11, 'Tanvi Rao', '2024-06-01');
ERROR 1364 (HY000): Field 'salary' doesn't have a default value

When to use it: on every column that the business genuinely requires. A good habit is to make columns NOT NULL by default and allow NULL only when "unknown" or "not applicable" is a real situation (like manager_id for the CEO). As the NULL Values chapter showed, every nullable column brings three-valued logic into your queries.


2. UNIQUE

UNIQUE means no two rows may have the same value in that column (or the same combination, for a multi-column UNIQUE).

Emails must be unique. Priya already uses priya@company.com:

INSERT INTO employees (emp_id, name, email, salary, hire_date)
VALUES (11, 'Tanvi Rao', 'priya@company.com', 50000, '2024-06-01');
ERROR 1062 (23000): Duplicate entry 'priya@company.com' for key 'employees.email'

UNIQUE Allows Many NULLs

Meera already has a NULL email. Can two more employees also have NULL?

INSERT INTO employees (emp_id, name, email, salary, hire_date)
VALUES (11, 'Tanvi Rao', NULL, 50000, '2024-06-01');
INSERT INTO employees (emp_id, name, email, salary, hire_date)
VALUES (12, 'Dev Malhotra', NULL, 52000, '2024-06-01');

SELECT emp_id, name, email FROM employees WHERE email IS NULL;
+--------+--------------+-------+
| emp_id | name         | email |
+--------+--------------+-------+
|      8 | Meera Nair   | NULL  |
|     11 | Tanvi Rao    | NULL  |
|     12 | Dev Malhotra | NULL  |
+--------+--------------+-------+
3 rows in set (0.00 sec)

Yes. Since NULL is not equal to NULL (it means "unknown"), the database cannot say two NULLs are duplicates. MySQL, PostgreSQL and Oracle all allow many NULLs in a UNIQUE column. SQL Server is the exception: it allows only one NULL in a UNIQUE constraint. If every row must have a unique value, use NOT NULL UNIQUE.

Uniqueness Follows the Collation

departments.dept_name is UNIQUE and "Sales" exists. Watch this:

INSERT INTO departments VALUES (60, 'sales', 'Chennai');
ERROR 1062 (23000): Duplicate entry 'sales' for key 'departments.dept_name'

With MySQL's default case-insensitive collation, "sales" and "Sales" are the same value, so it is a duplicate. That is usually what you want for names and emails.

Multi-Column UNIQUE

Sometimes a combination must be unique. Say two customers may share a name, but not a name and a city:

ALTER TABLE customers
  ADD CONSTRAINT uq_customers_name_city UNIQUE (name, city);

INSERT INTO customers VALUES (106, 'Rahul Verma', 'Mumbai', '2024-01-01');  -- OK
INSERT INTO customers VALUES (107, 'Rahul Verma', 'Delhi',  '2024-01-01');
ERROR 1062 (23000): Duplicate entry 'Rahul Verma-Delhi' for key 'customers.uq_customers_name_city'

The first insert worked because (Rahul Verma, Mumbai) is a new combination; the second failed because customer 101 is already (Rahul Verma, Delhi). Under the hood, MySQL implements every UNIQUE constraint as a unique index, which is also why it speeds up lookups on that column (see Indexes).


3. PRIMARY KEY

A PRIMARY KEY uniquely identifies each row. It is UNIQUE + NOT NULL, and a table can have only one primary key (which may consist of several columns).

INSERT INTO employees (emp_id, name, salary, hire_date)
VALUES (3, 'Copy Cat', 40000, '2024-06-01');
ERROR 1062 (23000): Duplicate entry '3' for key 'employees.PRIMARY'
INSERT INTO employees (emp_id, name, salary, hire_date)
VALUES (NULL, 'No Id', 40000, '2024-06-01');
ERROR 1048 (23000): Column 'emp_id' cannot be null

PRIMARY KEY vs UNIQUE

Point

PRIMARY KEY

UNIQUE

Duplicates allowed

No

No

NULL allowed

No, never

Yes, many NULLs (in MySQL)

How many per table

Exactly one (should always exist)

As many as you need

Purpose

The row's identity; target of foreign keys

Prevent duplicates in other natural keys (email, phone, code)

InnoDB storage

Clustered index: rows are physically stored in primary key order

Secondary index

In DBMS terms, the primary key is the candidate key you chose; the other candidate keys (like email) are usually declared UNIQUE.

Composite Primary Keys

To see composite keys and foreign key actions, let's create two new tables: projects, and which employees work on which project. Read through the definitions carefully; every constraint in this chapter appears here.

CREATE TABLE projects (
  project_id  INT AUTO_INCREMENT PRIMARY KEY,
  code        CHAR(6)       NOT NULL UNIQUE,
  title       VARCHAR(100)  NOT NULL,
  dept_id     INT,
  budget      DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (budget >= 0),
  start_date  DATE          NOT NULL,
  end_date    DATE,
  status      VARCHAR(10)   NOT NULL DEFAULT 'Planned',
  CONSTRAINT chk_projects_dates  CHECK (end_date >= start_date),
  CONSTRAINT chk_projects_status CHECK (status IN ('Planned', 'Active', 'Done')),
  CONSTRAINT fk_projects_dept FOREIGN KEY (dept_id)
      REFERENCES departments(dept_id)
      ON DELETE SET NULL
      ON UPDATE CASCADE
);

CREATE TABLE project_members (
  project_id INT,
  emp_id     INT,
  role       VARCHAR(30) NOT NULL DEFAULT 'Developer',
  PRIMARY KEY (project_id, emp_id),
  CONSTRAINT fk_members_project FOREIGN KEY (project_id)
      REFERENCES projects(project_id) ON DELETE CASCADE,
  CONSTRAINT fk_members_emp FOREIGN KEY (emp_id)
      REFERENCES employees(emp_id)
);

project_members is a classic junction table for a many-to-many relationship: one employee can be on many projects, and one project has many employees. Neither column alone is unique, but the pair (project_id, emp_id) is: the same person should not be added to the same project twice. That pair is the composite primary key.

Let's fill the tables:

INSERT INTO projects (code, title, dept_id, start_date)
VALUES ('PAY-01', 'Payroll Revamp', 30, '2024-04-01');
INSERT INTO projects (code, title, dept_id, budget, start_date, status)
VALUES ('CRM-01', 'CRM Rollout', 20, 500000, '2024-04-01', 'Active');
INSERT INTO projects (code, title, dept_id, budget, start_date, end_date)
VALUES ('WEB-01', 'New Website', 40, 300000, '2024-05-01', '2024-12-31');
INSERT INTO projects (code, title, dept_id, budget, start_date)
VALUES ('RND-01', 'Battery Research', 50, 750000, '2024-07-01');

INSERT INTO project_members (project_id, emp_id) VALUES (1, 7), (1, 8);
INSERT INTO project_members VALUES (2, 4, 'Lead'), (2, 5, 'Developer'), (2, 6, 'Tester');
INSERT INTO project_members (project_id, emp_id) VALUES (3, 4);

SELECT * FROM project_members ORDER BY project_id, emp_id;
+------------+--------+-----------+
| project_id | emp_id | role      |
+------------+--------+-----------+
|          1 |      7 | Developer |
|          1 |      8 | Developer |
|          2 |      4 | Lead      |
|          2 |      5 | Developer |
|          2 |      6 | Tester    |
|          3 |      4 | Developer |
+------------+--------+-----------+
6 rows in set (0.00 sec)

Employee 4 appears twice and project 2 appears three times, which is fine. But adding Sneha to project 2 again breaks the composite key:

INSERT INTO project_members VALUES (2, 4, 'Lead');
ERROR 1062 (23000): Duplicate entry '2-4' for key 'project_members.PRIMARY'

The column-level UNIQUE on code works the same way: INSERT INTO projects (code, title, dept_id, budget, start_date) VALUES ('PAY-01', 'Duplicate code', 30, 1000, '2024-07-01'); fails with ERROR 1062 (23000): Duplicate entry 'PAY-01' for key 'projects.code'.

Also note that we never wrote NOT NULL on project_id and emp_id, yet SHOW CREATE TABLE project_members shows them as int NOT NULL. Every primary key column is automatically NOT NULL.


4. DEFAULT

DEFAULT gives the value a column receives when an INSERT does not provide one.

The first project insert above did not mention budget or status:

SELECT project_id, code, title, dept_id, budget, end_date, status FROM projects;
+------------+--------+------------------+---------+-----------+------------+---------+
| project_id | code   | title            | dept_id | budget    | end_date   | status  |
+------------+--------+------------------+---------+-----------+------------+---------+
|          1 | PAY-01 | Payroll Revamp   |      30 |      0.00 | NULL       | Planned |
|          2 | CRM-01 | CRM Rollout      |      20 | 500000.00 | NULL       | Active  |
|          3 | WEB-01 | New Website      |      40 | 300000.00 | 2024-12-31 | Planned |
|          4 | RND-01 | Battery Research |      50 | 750000.00 | NULL       | Planned |
+------------+--------+------------------+---------+-----------+------------+---------+
4 rows in set (0.00 sec)

PAY-01 got budget = 0.00 and status = 'Planned' from the defaults. end_date has no default and allows NULL, so it became NULL. project_id came from AUTO_INCREMENT, a MySQL column attribute that works like an automatic, ever-increasing default.

A default is used only when the column is omitted or you write the keyword DEFAULT. An explicit NULL is not replaced by the default:

ALTER TABLE employees ALTER COLUMN city SET DEFAULT 'Bengaluru';

INSERT INTO employees (emp_id, name, salary, hire_date)
VALUES (13, 'Nikhil Jain', 60000, '2024-08-01');          -- city omitted
INSERT INTO employees (emp_id, name, salary, hire_date, city)
VALUES (14, 'Pooja Shah', 61000, '2024-08-01', DEFAULT);  -- DEFAULT keyword
INSERT INTO employees (emp_id, name, salary, hire_date, city)
VALUES (15, 'Zoya Khan', 62000, '2024-08-01', NULL);      -- explicit NULL

SELECT emp_id, name, city FROM employees WHERE emp_id >= 13;
+--------+-------------+-----------+
| emp_id | name        | city      |
+--------+-------------+-----------+
|     13 | Nikhil Jain | Bengaluru |
|     14 | Pooja Shah  | Bengaluru |
|     15 | Zoya Khan   | NULL      |
+--------+-------------+-----------+
3 rows in set (0.00 sec)

Common defaults in real tables: created_at DATETIME DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT TRUE, quantity INT DEFAULT 1.


5. CHECK

CHECK (condition) rejects any row for which the condition is FALSE.

CHECK constraints are enforced in MySQL 8.0.16 and later. Older MySQL versions accepted the syntax but silently ignored it, which is a classic trap if you read old tutorials or work on an old server.

Our projects table has three checks. Each of these inserts (for a new marketing project) breaks one:

INSERT INTO projects (code, title, dept_id, budget, start_date)
VALUES ('MKT-01', 'Ad Campaign', 40, -500, '2024-04-01');
ERROR 3819 (HY000): Check constraint 'projects_chk_1' is violated.
INSERT INTO projects (code, title, dept_id, budget, start_date, end_date)
VALUES ('MKT-01', 'Ad Campaign', 40, 50000, '2024-04-01', '2024-03-01');
ERROR 3819 (HY000): Check constraint 'chk_projects_dates' is violated.
INSERT INTO projects (code, title, dept_id, budget, start_date, status)
VALUES ('MKT-01', 'Ad Campaign', 40, 50000, '2024-04-01', 'Started');
ERROR 3819 (HY000): Check constraint 'chk_projects_status' is violated.

Compare the messages. chk_projects_dates and chk_projects_status tell you immediately which rule failed. projects_chk_1 is the name MySQL generated for the unnamed column-level check on budget; you would have to look it up. This is why you should name your constraints. A common convention is pk_, uq_, fk_, chk_ + table + column(s).

CHECK and NULL: UNKNOWN Passes

Look back at the projects table: CRM-01 has end_date = NULL. Why didn't CHECK (end_date >= start_date) reject it?

SELECT NULL >= '2024-01-01' AS check_result;
+--------------+
| check_result |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

The comparison is UNKNOWN. And here is a rule worth memorising, because it is the opposite of WHERE:

WHERE keeps a row only if the condition is TRUE. CHECK rejects a row only if the condition is FALSE. So UNKNOWN passes a CHECK.

That is exactly what we want here: an ongoing project has no end date yet. If you need to forbid NULL, add NOT NULL; CHECK alone will not do it.

CHECK Rules in MySQL

  • The condition can use several columns of the same row, as chk_projects_dates does.

  • It cannot contain subqueries, so it cannot look at other rows or other tables (that is what foreign keys and triggers are for).

  • It cannot use non-deterministic functions like NOW() or RAND(), or refer to an AUTO_INCREMENT column.

  • A CHECK can be created NOT ENFORCED: it is stored but not checked (useful only while migrating data).


6. FOREIGN KEY

A FOREIGN KEY makes a column (the child) refer to a PRIMARY KEY or UNIQUE column of another table (the parent). Every non-NULL value in the child must exist in the parent.

The table holding the foreign key is the child (or referencing) table; the table it points to is the parent (or referenced) table. In company_db, employees is a child of departments, orders is a child of customers, and employees is even a child of itself through manager_id.

What a Foreign Key Prevents

Inserting a child that points to nothing:

INSERT INTO projects (code, title, dept_id, budget, start_date)
VALUES ('AI-01', 'Chatbot', 99, 100000, '2024-06-01');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company_db`.`projects`, CONSTRAINT `fk_projects_dept` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`) ON DELETE SET NULL ON UPDATE CASCADE)

There is no department 99. Note that a NULL dept_id would be accepted: a NULL foreign key means "no parent", not "an invalid parent". (That is how Ishita, with no department, exists in employees.)

The other direction is controlled by the referential actions you choose: what should happen to children when their parent is deleted or its key is updated?

Referential Actions: ON DELETE and ON UPDATE

Action

What happens to the child rows

Typical use

RESTRICT

The parent change is refused while children exist

Orders of a customer: never lose them silently

NO ACTION

Standard SQL defers the check to the end of the statement; in MySQL InnoDB it behaves exactly like RESTRICT

Same as RESTRICT

CASCADE

Children are deleted too (ON DELETE) or get the new key (ON UPDATE)

Rows that cannot exist without the parent: order items, project memberships

SET NULL

Children's foreign key becomes NULL (column must be nullable)

Optional links: a project whose department closes

SET DEFAULT

Recognised by the parser but not implemented by InnoDB. MySQL 8.4 even lets you create such a key without a warning, but deleting the parent is then simply refused (error 1451), exactly like RESTRICT

Not usable in MySQL

If you write no action at all, MySQL uses NO ACTION (i.e. restrict), which is why our earlier deletes of departments and customers failed. Let's see the other actions work.

ON DELETE CASCADE in Action

fk_members_project has ON DELETE CASCADE. The CRM project (id 2) has three members. Delete the project:

DELETE FROM projects WHERE project_id = 2;
SELECT * FROM project_members ORDER BY project_id, emp_id;
+------------+--------+-----------+
| project_id | emp_id | role      |
+------------+--------+-----------+
|          1 |      7 | Developer |
|          1 |      8 | Developer |
|          3 |      4 | Developer |
+------------+--------+-----------+
3 rows in set (0.00 sec)

MySQL reported "1 row affected" for the DELETE, but the three membership rows of project 2 disappeared with it. Cascades are convenient and dangerous at the same time: a single DELETE can quietly remove rows from many tables, through chains of cascades. Use CASCADE only for child rows that truly have no meaning without their parent. (Also note: in MySQL, rows removed by a cascade do not fire DELETE triggers on the child table.)

ON UPDATE CASCADE and ON DELETE SET NULL

fk_projects_dept has both. RND-01 belongs to department 50 (Research), which has no employees. Change Research's id:

UPDATE departments SET dept_id = 55 WHERE dept_id = 50;
SELECT project_id, code, dept_id FROM projects;
+------------+--------+---------+
| project_id | code   | dept_id |
+------------+--------+---------+
|          1 | PAY-01 |      30 |
|          3 | WEB-01 |      40 |
|          4 | RND-01 |      55 |
+------------+--------+---------+
3 rows in set (0.00 sec)

The project followed its department to the new id automatically. Now close the department:

DELETE FROM departments WHERE dept_id = 55;
SELECT project_id, code, dept_id FROM projects;
+------------+--------+---------+
| project_id | code   | dept_id |
+------------+--------+---------+
|          1 | PAY-01 |      30 |
|          3 | WEB-01 |      40 |
|          4 | RND-01 |    NULL |
+------------+--------+---------+
3 rows in set (0.00 sec)

The project survives, now without a department. (Updating department 40 the same way would still fail, because employees_ibfk_1 has no ON UPDATE action and Arjun is in department 40. Every foreign key pointing at a row gets a say.)

RESTRICT in Action

fk_members_emp has no action, so it restricts. Meera (8) is on project 1:

DELETE FROM employees WHERE emp_id = 8;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company_db`.`project_members`, CONSTRAINT `fk_members_emp` FOREIGN KEY (`emp_id`) REFERENCES `employees` (`emp_id`))

Ananya (6) was only on project 2, whose memberships were cascaded away, so DELETE FROM employees WHERE emp_id = 6 now succeeds.

Foreign Key Requirements

MySQL is strict about how foreign keys are defined:

  • Matching types. The child and parent columns must have compatible types (same integer type and signedness, same character set for strings):

CREATE TABLE bad_child (
  id INT PRIMARY KEY,
  dept_id BIGINT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
ERROR 3780 (HY000): Referencing column 'dept_id' and referenced column 'dept_id' in foreign key constraint 'bad_child_ibfk_1' are incompatible.
  • The parent column must be a key (PRIMARY KEY or UNIQUE). Pointing at departments.location, which is not unique, fails with ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'bad_child2_ibfk_1' in the referenced table 'departments'.

  • An index on the child column is created automatically if one does not already exist. SHOW CREATE TABLE project_members shows KEY `fk_members_emp` (`emp_id`), which we never wrote. That index makes the parent-side checks fast.

  • Both tables must use the InnoDB engine (the default). The older MyISAM engine ignores foreign keys.

Turning Checks Off (and Why It's Risky)

When bulk-loading data where children may arrive before parents, people run SET FOREIGN_KEY_CHECKS = 0;. While it is off, MySQL accepts anything:

SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO orders VALUES (1009, 999, '2024-05-01', 100, 'Pending');  -- no customer 999!
SET FOREIGN_KEY_CHECKS = 1;

The insert succeeds, and turning checks back on does not re-validate existing rows. Order 1009 now points to a customer that does not exist: exactly the orphan row foreign keys are meant to prevent. Use this switch only for controlled data loads where you know the data is consistent.

Let's remove that orphan before continuing, otherwise the foreign key we re-create at the end of this chapter would fail with error 1452:

DELETE FROM orders WHERE order_id = 1009;
Query OK, 1 row affected (0.00 sec)

Adding and Removing Constraints Later

Constraints can be added to existing tables with ALTER TABLE (introduced in ALTER, DROP & TRUNCATE). The key thing to understand: existing data is checked when you add a constraint.

ALTER TABLE employees
  ADD CONSTRAINT chk_emp_salary CHECK (salary >= 50000);
ERROR 3819 (HY000): Check constraint 'chk_emp_salary' is violated.

Meera earns 48000, so this rule is already broken and MySQL refuses to add it. A rule the current data satisfies works, and is enforced from then on:

ALTER TABLE employees
  ADD CONSTRAINT chk_emp_salary CHECK (salary > 0);

UPDATE employees SET salary = -1 WHERE emp_id = 8;
ERROR 3819 (HY000): Check constraint 'chk_emp_salary' is violated.

The same applies to UNIQUE (fails if duplicates exist), NOT NULL (fails if NULLs exist) and FOREIGN KEY (fails if orphan values exist). Clean the data first, then add the constraint.

The syntax for each operation in MySQL:

Task

MySQL syntax

Add NOT NULL

ALTER TABLE t MODIFY col type NOT NULL

Add UNIQUE

ALTER TABLE t ADD CONSTRAINT uq_name UNIQUE (col, ...)

Drop UNIQUE

ALTER TABLE t DROP INDEX uq_name

Add PRIMARY KEY

ALTER TABLE t ADD PRIMARY KEY (col, ...)

Drop PRIMARY KEY

ALTER TABLE t DROP PRIMARY KEY

Add FOREIGN KEY

ALTER TABLE t ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES p(col) [ON DELETE ...]

Drop FOREIGN KEY

ALTER TABLE t DROP FOREIGN KEY fk_name

Add CHECK

ALTER TABLE t ADD CONSTRAINT chk_name CHECK (condition)

Drop CHECK

ALTER TABLE t DROP CHECK chk_name

Set / drop DEFAULT

ALTER TABLE t ALTER col SET DEFAULT v / DROP DEFAULT

To change a foreign key's ON DELETE rule, there is no "modify": you drop it and add it again. Here we replace orders_ibfk_1 with a properly named key:

ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id) ON DELETE RESTRICT;
Query OK, 8 rows affected (0.07 sec)
Records: 8  Duplicates: 0  Warnings: 0

"8 rows affected" is because MySQL rebuilt the orders table to add the key, copying all 8 existing orders and checking each one against customers. (Had the orphan order 1009 still been there, this ALTER would have failed with error 1452.) If you had used the auto-generated name without knowing it, you would first need SHOW CREATE TABLE orders to find it: one more reason to name constraints yourself.


Constraints at a Glance

Constraint

Guarantees

NULL allowed?

Per table

Violation error (MySQL)

NOT NULL

A value is always present

No

Any columns

1048 / 1364

UNIQUE

No duplicate values / combinations

Yes, many

Many

1062

PRIMARY KEY

Unique, non-null row identity

No

One

1062 / 1048

FOREIGN KEY

Values exist in the parent table

Yes (means "no parent")

Many

1452 (child) / 1451 (parent)

CHECK

Row satisfies a condition

UNKNOWN passes

Many

3819

DEFAULT

Value used when none is supplied

Not a restriction

One per column

None


Common Mistakes

Mistake

What happens

Fix

Relying only on application code for validation

Bad data enters through scripts, other apps, bugs

Put the rules in constraints too

Not naming constraints

Errors mention projects_chk_1; dropping needs a lookup

CONSTRAINT chk_projects_budget CHECK (...)

Expecting CHECK to reject NULL

UNKNOWN passes the check

Add NOT NULL as well

Expecting UNIQUE to allow only one NULL

MySQL allows many

Use NOT NULL UNIQUE

Using ON DELETE CASCADE everywhere

One DELETE silently wipes related data

Cascade only true "part-of" children; RESTRICT the rest

Foreign key column type differs from parent

Error 3780

Use identical types (INT to INT, same signedness)

Using CHECK on MySQL older than 8.0.16

Silently ignored

Upgrade, or use triggers

Leaving FOREIGN_KEY_CHECKS = 0 during normal work

Orphan rows; not re-checked later

Turn it back on immediately after a controlled load


Interview Questions

What are constraints and why do we need them?

Rules enforced by the database on table data (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT). They guarantee data integrity regardless of which application or user writes the data.

Difference between PRIMARY KEY and UNIQUE?

Both forbid duplicates. A primary key also forbids NULL and there is only one per table; it identifies the row and is the usual target of foreign keys. A table can have many UNIQUE constraints, and they allow NULLs (many, in MySQL).

Can a UNIQUE column have multiple NULLs?

In MySQL, PostgreSQL and Oracle, yes, because NULLs are not considered equal. SQL Server allows only one NULL in a UNIQUE constraint.

What is a composite key?

A key made of two or more columns whose combination is unique, e.g. (project_id, emp_id) in a junction table. Individually the columns may repeat.

What is a foreign key? Can it be NULL?

A column whose values must exist in a referenced PRIMARY KEY/UNIQUE column of a parent table. It can be NULL (unless declared NOT NULL); NULL means "no related row".

Explain ON DELETE CASCADE vs SET NULL vs RESTRICT.

When a parent row is deleted: CASCADE deletes the child rows too, SET NULL sets the children's foreign key to NULL, and RESTRICT (or NO ACTION, the default in MySQL) refuses the delete while children exist.

Does a CHECK constraint reject NULL values?

No. CHECK only rejects rows where the condition is FALSE. A comparison with NULL is UNKNOWN, which passes. Use NOT NULL to forbid NULLs.

What happens when you add a constraint to a table that already has violating data?

MySQL validates existing rows and refuses to add the constraint (e.g. error 3819 for CHECK, 1062 for UNIQUE). You must clean the data first.

How many primary keys can a table have? How many foreign keys?

One primary key (possibly composite). Any number of foreign keys.

What is the difference between a constraint and an index?

A constraint is a logical rule; an index is a physical structure for fast lookup. MySQL implements PRIMARY KEY and UNIQUE using indexes, and creates an index for each foreign key, but you can also create ordinary indexes that enforce nothing.


Summary

  • Constraints are rules the database enforces on every write, protecting domain, entity and referential integrity.

  • NOT NULL: a value is required. DEFAULT: the value used when none is given (not when NULL is given explicitly).

  • UNIQUE: no duplicates; many NULLs allowed in MySQL; can span several columns.

  • PRIMARY KEY: UNIQUE + NOT NULL, one per table, can be composite; InnoDB stores rows in PK order.

  • FOREIGN KEY: child values must exist in the parent; ON DELETE / ON UPDATE choose RESTRICT (default), CASCADE or SET NULL.

  • CHECK: enforced from MySQL 8.0.16; rejects only FALSE, so NULL passes.

  • Name your constraints, and remember that adding a constraint validates existing data.

With data safely in our tables, it is time to start summarising it. In the next chapter, Aggregate Functions, we will count, total, average and find minimums and maximums with COUNT, SUM, AVG, MIN and MAX.