Constraints and Keys

Why Constraints Matter

Constraints are rules that protect the integrity of your data. They stop invalid rows from ever being written.

Common Constraint Types

• PRIMARY KEY: unique identifier for each row; a table should have exactly one.
• FOREIGN KEY: guarantees a value exists in another table.
• UNIQUE: no duplicate values in that column.
• NOT NULL: the column must always have a value.
• CHECK: a condition every row must satisfy (enforced from MySQL 8.0.16).
• DEFAULT: value used when a column is omitted.

Foreign Keys in Action

CREATE TABLE orders (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  total       DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_order_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
      ON DELETE CASCADE
);

ON DELETE CASCADE deletes the child rows automatically when the parent row is removed. Alternatives are SET NULL and RESTRICT.

Adding a Constraint Later

ALTER TABLE orders ADD CONSTRAINT fk_order_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);
ALTER TABLE customers ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');

Key Points

• Constraints protect data correctness at the database level.
• PRIMARY KEY and FOREIGN KEY drive relational integrity.
• ON DELETE CASCADE handles related deletions automatically.
• CHECK conditions are now enforced by MySQL 8.