Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 40 ยท Data Types and Storage Engines

Primary Keys, Foreign Keys and Constraints in MariaDB

Keep MariaDB data correct with PRIMARY KEY, AUTO_INCREMENT, FOREIGN KEY with ON DELETE rules, UNIQUE, NOT NULL, DEFAULT and CHECK constraints, and learn how to add and drop them safely.

Constraints are rules the database enforces for you. A well-constrained schema rejects impossible data (an order for a customer who does not exist, two accounts with the same email, a negative price) no matter which application, script or person writes to it. This lesson covers every constraint MariaDB offers, with runnable examples that behave like the real thing.

PRIMARY KEY

A primary key identifies each row. It must be unique and NOT NULL, and a table has at most one.

SQL
CREATE TABLE customers (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  PRIMARY KEY (id)
);

With InnoDB the primary key is the clustered index: rows are physically stored in key order. A short, ever-increasing integer is the best choice. A composite key spans several columns, as in a join table:

SQL
CREATE TABLE enrollments (
  student_id INT UNSIGNED NOT NULL,
  course_id  INT UNSIGNED NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

AUTO_INCREMENT generates the next integer on insert. Gaps are normal (rolled-back inserts, deleted rows) and the counter is not reused after DELETE. Read the newest value with LAST_INSERT_ID(); reset the counter with ALTER TABLE t AUTO_INCREMENT = 1000;.

SQL Primary key and auto numbering

Press Run (or Ctrl + Enter) to see the result here.

(The commented statement fails on purpose when you enable it.)

UNIQUE

UNIQUE forbids duplicate values in a column or a combination of columns, and creates an index. Unlike a primary key it may be nullable, and NULL values are allowed to repeat.

SQL
ALTER TABLE customers ADD CONSTRAINT uq_customers_email UNIQUE (email);
ALTER TABLE seats ADD UNIQUE KEY uq_show_seat (show_id, seat_no);

Index prefixes matter with utf8mb4: a VARCHAR(255) unique key may exceed the byte limit on older settings, hence VARCHAR(190) in many schemas.

NOT NULL and DEFAULT

NOT NULL rejects missing values. DEFAULT fills a value when you omit the column. Together they make inserts short and safe:

SQL
status   VARCHAR(20)   NOT NULL DEFAULT 'pending',
quantity INT UNSIGNED  NOT NULL DEFAULT 1,
created  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP

FOREIGN KEY

A foreign key makes a column point at a primary (or unique) key in another table, and the database refuses rows that break the link. It requires InnoDB and identical types on both sides:

SQL
CREATE TABLE orders (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB;

The ON DELETE and ON UPDATE actions say what happens to child rows when the parent changes:

Action Effect on child rows
RESTRICT / NO ACTION Refuse the parent change (default)
CASCADE Delete or update the children too
SET NULL Set the child column to NULL (column must be nullable)
SET DEFAULT Not supported by InnoDB

Use CASCADE carefully: deleting one customer could silently delete orders, invoices and payments. RESTRICT is the safest default, and "soft delete" columns (deleted_at) avoid the question.

SQLite needs the PRAGMA foreign_keys = ON switch, and then behaves like InnoDB here:

SQL A foreign key blocks orphan rows

Press Run (or Ctrl + Enter) to see the result here.

SQL ON DELETE CASCADE

Press Run (or Ctrl + Enter) to see the result here.

If you load data in bulk, or restore dumps, MariaDB can temporarily skip the checks with SET foreign_key_checks = 0;. Turn it back on afterwards, and check the data yourself, because skipped checks let bad data in.

CHECK

A CHECK constraint validates a condition on each row. MariaDB enforces it since 10.2.1 (older versions parsed and ignored it):

SQL
CREATE TABLE products (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL,
  CONSTRAINT chk_price CHECK (price >= 0),
  CONSTRAINT chk_stock CHECK (stock >= 0)
);
SQL CHECK rejects bad rows

Press Run (or Ctrl + Enter) to see the result here.

Adding, listing and dropping constraints

Name your constraints, so that you can drop them later and so error messages are readable:

SQL
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id);
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;
ALTER TABLE products DROP CONSTRAINT chk_price;
ALTER TABLE customers DROP INDEX uq_customers_email;
ALTER TABLE customers DROP PRIMARY KEY;

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = DATABASE() AND table_name = 'orders';

Adding a constraint to a table that already has bad rows fails; clean the data first (find orphans with a LEFT JOIN ... WHERE parent.id IS NULL).

flowchart LR
  C["customers (PK id)"] -->|"1 : N"| O["orders (FK customer_id)"]
  O -->|"1 : N"| I["order_items (FK order_id)"]
  P["products (PK id)"] -->|"1 : N"| I

Foreign keys express the relationships in the schema.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What happens to the AUTO_INCREMENT counter when you delete the last row?

    Deleting rows does not rewind the counter, so gaps appear. TRUNCATE (or ALTER TABLE ... AUTO_INCREMENT) resets it.
  2. 2. Which foreign key action refuses to delete a parent row that still has children?

    RESTRICT (and NO ACTION) block the change; CASCADE would delete the children too.
  3. 3. Can a UNIQUE column hold several NULL values in MariaDB?

    Because NULL means unknown, two NULLs do not violate uniqueness.
  4. 4. Since which version does MariaDB actually enforce CHECK constraints?

    Older MariaDB and MySQL 5.7 parsed CHECK but ignored it.
  5. 5. What must match on both sides of a foreign key?

    Mismatched types are the most common cause of errno 150 / "cannot add foreign key constraint".

Summary

  • PRIMARY KEY identifies rows (use a short integer with AUTO_INCREMENT); UNIQUE forbids duplicates.
  • NOT NULL, DEFAULT and CHECK keep single rows valid.
  • FOREIGN KEY ... ON DELETE/UPDATE keeps tables consistent; prefer RESTRICT, use CASCADE knowingly.
  • Name constraints so you can drop them; types on both sides of a foreign key must match.

Next: Storage engines