Constraints, Keys, and AUTO_INCREMENT

Constraints are rules enforced by MySQL to keep data valid:

Concept

Constraints are rules enforced by MySQL to keep data valid:

Unique clustered index InnoDB stores rows in PK order; one per table Multiple NULLs allowed

Strict mode rejects NULL; non-strict converts to implicit default with warning (for multi-row inserts)

Expression defaults in parentheses since 8.0.13 Requires an index on referencing and referenced columns (MySQL creates one on the child if missing) Enforced since 8.0.16 Parsed but ignored in earlier versions One per table; must be indexed

Why It Matters

Constraints guarantee integrity no matter which application or script writes the data. In MySQL, version and configuration (storage engine, SQL mode, foreign_key_checks) affect whether constraints are actually enforced — a common source of surprises.

Syntax

CREATE TABLE child (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id  INT UNSIGNED NOT NULL,
    code       VARCHAR(20)  NOT NULL,
    qty        INT          NOT NULL DEFAULT 1,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    uid        BINARY(16)   NOT NULL DEFAULT (UUID_TO_BIN(UUID())),   -- expression default (8.0.13+)
    PRIMARY KEY (id),
    UNIQUE KEY uq_child_code (code),
    CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT chk_child_qty CHECK (qty > 0)
) ENGINE = InnoDB;

Example

7.1 PRIMARY KEY If a table has no primary key, InnoDB uses the first UNIQUE NOT NULL index, or creates a hidden 6-byte row ID — which cannot be used by queries or replication efficiently. Always declare a PK. MySQL 8.0.30+ can generate an invisible primary key automatically (sql_generate_invisible_primary_key = ON).

7.2 FOREIGN KEY and referential actions Supported in InnoDB RESTRICT / NO ACTION (default)

Yes (identical in InnoDB — checked immediately) Reject parent delete/update if children exist Delete/update children too

Set child FK to NULL (column must be nullable) No (parsed but rejected by InnoDB)

FK requirements in MySQL: both tables InnoDB; columns with the same type, sign, and (for strings) charset/collation; the parent column must be indexed (PK or UNIQUE recommended). Otherwise you get ERROR 3780 / 1822 ("incompatible" or "missing index").

Disabling checks temporarily (bulk loads, restoring dumps) — use with great care:

7.4 NOT NULL and SQL mode 7.5 CHECK constraints (8.0.16+) Rules: a CHECK accepts rows where the condition is TRUE or NULL; it cannot use subqueries, stored functions, non-deterministic functions (NOW(), RAND()), or reference other tables; it cannot reference an AUTO_INCREMENT column. ALTER TABLE... ALTER CHECK chk_name NOT ENFORCED disables a check without dropping it.

7.6 DEFAULT values Behavior worth knowing:

Gaps are normal: rolled-back inserts, failed inserts, INSERT IGNORE, and bulk-insert pre-allocation consume values.

Since 8.0 the counter is persisted across restarts (5.7 recalculated it as MAX(id)+1, which could reuse IDs of deleted rows).

auto_increment_increment / auto_increment_offset let multiple writers generate non-overlapping IDs (e.g., odd/even).

innodb_autoinc_lock_mode = 2 (interleaved, default in 8.0) gives the best concurrency with row-based replication.

LAST_INSERT_ID() is per connection, so it is safe under concurrency.

7.8 Viewing and managing constraints

CREATE TABLE ecu (
    ecu_id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id  INT UNSIGNED NOT NULL,
    ecu_code    VARCHAR(10)  NOT NULL,
    serial_no   VARCHAR(30)  NOT NULL,
    PRIMARY KEY (ecu_id),
    UNIQUE KEY uq_ecu_serial (serial_no),
    UNIQUE KEY uq_ecu_per_vehicle (vehicle_id, ecu_code)       -- composite unique
);

CREATE TABLE ecu_software (
    install_id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ecu_id         INT UNSIGNED NOT NULL,
    version_label  VARCHAR(20)  NOT NULL,
    installed_at   DATETIME     NOT NULL,
    CONSTRAINT fk_install_ecu FOREIGN KEY (ecu_id) REFERENCES ecu (ecu_id)
        ON DELETE CASCADE          -- deleting an ECU deletes its install history
        ON UPDATE RESTRICT
);

INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
VALUES (2001, 99, '2026-04-01', 'PENDING', 100);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (...)

DELETE FROM customers WHERE customer_id = 1;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (...)

SET foreign_key_checks = 0;     -- session only
-- ... load data ...
SET foreign_key_checks = 1;     -- existing bad rows are NOT re-validated!

ALTER TABLE customers ADD CONSTRAINT uq_customers_email UNIQUE (email);
-- Multiple rows with email = NULL are allowed; duplicates of non-NULL values are rejected:
-- ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'customers.uq_customers_email'

CREATE TABLE nn_demo (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL);
INSERT INTO nn_demo (id) VALUES (1);
-- Strict mode: ERROR 1364 (HY000): Field 'name' doesn't have a default value
-- Non-strict mode: row inserted with name = '' and a warning  (dangerous)

CREATE TABLE test_result (
    test_id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    result      VARCHAR(8) NOT NULL,
    duration_s  INT        NOT NULL,
    started_at  DATETIME   NOT NULL,
    ended_at    DATETIME   NULL,
    CONSTRAINT chk_tr_result   CHECK (result IN ('PASS', 'FAIL', 'BLOCKED')),
    CONSTRAINT chk_tr_duration CHECK (duration_s >= 0),
    CONSTRAINT chk_tr_times    CHECK (ended_at IS NULL OR ended_at >= started_at)
);

INSERT INTO test_result (result, duration_s, started_at) VALUES ('SKIP', 10, NOW());
-- ERROR 3819 (HY000): Check constraint 'chk_tr_result' is violated.

CREATE TABLE tickets (
    ticket_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status      VARCHAR(10) NOT NULL DEFAULT 'OPEN',
    priority    TINYINT     NOT NULL DEFAULT 3,
    created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    due_date    DATE        NOT NULL DEFAULT (CURRENT_DATE + INTERVAL 7 DAY),   -- expression default
    tags        JSON        NOT NULL DEFAULT (JSON_ARRAY())                      -- JSON default needs an expression
);
INSERT INTO tickets () VALUES ();
SELECT * FROM tickets\G

CREATE TABLE dtc_events (
    dtc_event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    dtc_code     VARCHAR(8) NOT NULL
) AUTO_INCREMENT = 1000;

INSERT INTO dtc_events (dtc_code) VALUES ('P0301'), ('U0100');
SELECT LAST_INSERT_ID();        -- 1000  (first ID generated by the LAST INSERT statement in this session)
SELECT * FROM dtc_events;       -- 1000 P0301, 1001 U0100

ALTER TABLE dtc_events AUTO_INCREMENT = 5000;   -- next value (cannot be set below MAX(id)+1)
SHOW TABLE STATUS LIKE 'dtc_events'\G           -- Auto_increment: 5000

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'eh_company' AND table_name = 'order_items';

SELECT constraint_name, table_name, referenced_table_name, delete_rule, update_rule
FROM information_schema.referential_constraints
WHERE constraint_schema = 'eh_company';

ALTER TABLE order_items DROP FOREIGN KEY fk_items_product;     -- FKs: DROP FOREIGN KEY
ALTER TABLE order_items DROP CHECK chk_items_qty;              -- or DROP CONSTRAINT (8.0.19+)
ALTER TABLE customers  DROP INDEX uq_customers_email;           -- UNIQUE constraints are indexes
ALTER TABLE customers  DROP PRIMARY KEY;                        -- (only if no AUTO_INCREMENT/FKs depend on it)

Expected Result

SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'eh_company' AND table_name = 'order_items'
ORDER BY constraint_type, constraint_name;

+------------------+-----------------+
| CONSTRAINT_NAME  | CONSTRAINT_TYPE |
+------------------+-----------------+
| chk_items_qty    | CHECK           |
| fk_items_order   | FOREIGN KEY     |
| fk_items_product | FOREIGN KEY     |
| PRIMARY          | PRIMARY KEY     |
+------------------+-----------------+

Common Mistakes

Using MyISAM and wondering why FKs are ignored.

FK column type mismatch (INT vs INT UNSIGNED, BIGINT vs INT).

Expecting ON DELETE SET DEFAULT to work in InnoDB.

Relying on CHECK constraints on MySQL < 8.0.16 (silently ignored).

Leaving foreign_key_checks = 0 on, or importing orphaned rows while it is off.

Assuming AUTO_INCREMENT values are gapless.

Running non-strict SQL mode, which turns constraint violations into warnings.

Best Practices

InnoDB + strict SQL mode + explicit constraint names.

Index every FK column (MySQL does it automatically if missing, but name it yourself).

Prefer RESTRICT; use CASCADE only for true ownership (order → order lines).

Use BIGINT UNSIGNED for fast-growing tables.

Interview Relevance

High: "Does MySQL enforce CHECK constraints?", "What happens if an InnoDB table has no PK?", "Why do AUTO_INCREMENT gaps appear?", "How does LAST_INSERT_ID() behave with multi-row inserts?", "Which FK actions does InnoDB support?".

Practice Questions

Create software_version(sw_version_id, ecu_code, version_label, release_date) with a unique (ecu_code, version_label).

Add a CHECK that release_date is not before 2000-01-01.

Create a child table that sets vehicle_id to NULL when a vehicle is deleted.

Insert three rows in one statement into an AUTO_INCREMENT table and explain what LAST_INSERT_ID() returns.

Find all foreign keys in eh_company and their delete rules.