Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 75 ยท Tables, Constraints and Schema Design

Keys and Constraints

Learn MySQL PRIMARY KEY, UNIQUE, NOT NULL and CHECK constraints (enforced since 8.0.16), constraint naming and invisible columns, with live examples.

A constraint is a rule that the database enforces on every write, no matter which program sends the statement. Your application may validate input, but scripts, admin tools and future code all write to the same tables. Constraints are the last line of defense that cannot be bypassed by accident.

The general idea is covered in the SQL constraints lesson. Here we focus on how MySQL treats each constraint: how InnoDB uses the primary key, what UNIQUE does with NULL, why CHECK was silently ignored before 8.0.16, how to name constraints, and what the INVISIBLE column option does. Foreign keys, the fifth kind, get their own lesson next.

The constraints MySQL enforces

PRIMARY KEY One unique, non-null identity per row.
UNIQUE No duplicates, NULLs allowed.
NOT NULL A value is always required.
CHECK Any condition a row must satisfy.

Foreign keys follow in the next lesson.

1 / 2

PRIMARY KEY

A primary key identifies each row. It is implicitly NOT NULL, it must be unique, and a table can have only one. In MySQL the primary key index is always named PRIMARY; you cannot choose another name.

In InnoDB the primary key is much more than a rule. InnoDB stores the table's rows inside the primary key's B-tree, so the table is physically ordered by it. This is called a clustered index. Three consequences follow:

  • Lookups by primary key are the fastest lookups the table offers.
  • Every secondary index stores a copy of the primary key value in each of its entries. A fat primary key (a long string, a composite of many columns) makes every index bigger.
  • If you declare no primary key, InnoDB picks the first UNIQUE index whose columns are all NOT NULL, or else invents a hidden 6-byte row ID. You cannot use that hidden column in queries, and it is shared by all such tables on the server, which can create a contention point on write-heavy tables.

So give every InnoDB table an explicit primary key. You can enforce that rule: with the setting sql_require_primary_key = ON (available since 8.0.13), CREATE TABLE fails for tables without one. Managed services often switch it on. MySQL 8.0.30 added sql_generate_invisible_primary_key, which makes the server add an invisible my_row_id key for you, as a safety net for tables you do not control.

A common choice is a surrogate integer key, BIGINT UNSIGNED AUTO_INCREMENT, covered in AUTO_INCREMENT, UUIDs and ID Strategies. A composite primary key uses several columns, which suits link tables:

SQL
PRIMARY KEY (student_id, course_id)
SQL A composite primary key stops duplicate pairs

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

The three rows insert, because only the pair has to be unique. The last statement stops with a UNIQUE constraint failed error. MySQL words it differently, ERROR 1062 (23000): Duplicate entry '1-10' for key 'enrollments.PRIMARY', but the effect is the same.

UNIQUE

A UNIQUE constraint forbids duplicates in a column or a combination of columns. Behind the scenes MySQL creates a unique index. Use it for every natural key that is not the primary key, such as email, username or sku.

Two MySQL specifics matter:

  • NULL is never equal to NULL, so a unique column may contain any number of NULL values. If you want "at most one row without a value", UNIQUE will not do that.
  • The index name is the column name unless you give one: a plain UNIQUE (email) creates an index called email, and a second one on the same column becomes email_2. Name your constraints (below) so error messages and EXPLAIN output stay readable.

There is also a size limit. An index key may be at most 3072 bytes with DYNAMIC rows. With utf8mb4, which uses up to 4 bytes per character, that is 768 characters, so a unique key over a VARCHAR(1000) fails with "Specified key was too long". Shorten the column, or index a hash of it stored in a generated column.

NOT NULL

NOT NULL is the simplest and most valuable constraint. It documents that a value always exists, and it lets the optimizer and your code skip NULL checks. In strict mode (see SQL Modes and Strictness), an INSERT that omits a NOT NULL column without a default fails with ERROR 1364 (HY000): Field 'name' doesn't have a default value. Without strict mode MySQL silently inserts a "zero" value such as '' or 0, which is one of the reasons to keep strict mode on.

NOT NULL does not reject empty strings. If '' is as bad as missing, add a CHECK.

CHECK

A CHECK constraint holds a condition that every row must satisfy. It is satisfied when the condition is true or unknown, so a NULL in a checked column passes, which is another reason to pair CHECK with NOT NULL.

Before MySQL 8.0.16 the parser accepted CHECK (...) and then ignored it. Tables created on 5.7 with CHECK clauses enforce nothing, and even after an upgrade nothing is retroactively verified. If you run an old schema, test that your checks really reject bad rows.

A check can be written on a single column or as a table constraint over several columns. It may only use deterministic expressions: no subqueries, no stored functions, no AUTO_INCREMENT columns and no functions such as NOW() or RAND(). A column-level check may refer only to its own column.

SQL
CREATE TABLE bookings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  starts_on DATE NOT NULL,
  ends_on   DATE NOT NULL,
  guests    TINYINT UNSIGNED NOT NULL CHECK (guests BETWEEN 1 AND 8),
  CONSTRAINT chk_bookings_dates CHECK (ends_on > starts_on)
);

The block is read-only because of the MySQL-only column types, but the constraints themselves work the same way in the live runner. You can also add and remove checks later: ALTER TABLE bookings DROP CHECK chk_bookings_dates; (MySQL also accepts DROP CONSTRAINT), and ALTER TABLE bookings ALTER CHECK chk_bookings_dates NOT ENFORCED; turns one off without losing its definition. Adding a check to a table full of rows scans the table, and fails if any existing row violates it.

Naming constraints

You name a constraint with CONSTRAINT name in front of it. If you do not, MySQL generates a name such as bookings_chk_1. The name is what appears in error messages and what you use to drop the constraint later, so write your own: chk_users_age, uq_users_email, fk_orders_customer. A consistent prefix makes them easy to find.

Two details: for a PRIMARY KEY the name is ignored and the index is always PRIMARY; for a UNIQUE constraint the name becomes the index name.

SQL Named constraints and what NULL does to UNIQUE

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

All three rows are accepted. User 2 has both a NULL phone and a NULL age, and neither rule objects. Now break the check on purpose and read the error, which carries the constraint's name:

SQL The error names the failed constraint

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

The run ends with CHECK constraint failed: chk_users_age. MySQL prints ERROR 3819 (HY000): Check constraint 'chk_users_age' is violated. A good name tells you what went wrong without looking at the schema.

A last example shows that NOT NULL accepts empty text, and how a check closes the gap:

SQL NOT NULL still allows empty strings

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

The empty string in a is accepted. To forbid it, write CHECK (a <> '') or CHECK (length(a) > 0).

Invisible columns

MySQL 8.0.23 added the INVISIBLE column option. An invisible column exists and works normally, but SELECT * does not return it, and INSERT without a column list does not expect a value for it. You reach it only by naming it.

SQL
-- MySQL 8.0.23 and later (read-only here)
CREATE TABLE accounts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  legacy_code VARCHAR(20) INVISIBLE
);

SELECT * FROM accounts;                    -- id, name
SELECT id, name, legacy_code FROM accounts; -- legacy_code only when named

The feature exists so you can add a column to a table without breaking applications that run SELECT * or positional inserts. Add the column as invisible, deploy the new code, and make it visible later with ALTER TABLE accounts ALTER COLUMN legacy_code SET VISIBLE;. A table must keep at least one visible column. The invisible primary key from sql_generate_invisible_primary_key uses the same mechanism.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should every InnoDB table have an explicit primary key?

    InnoDB clusters the table on the primary key and copies it into every secondary index. Without one, it falls back to a hidden row ID that you cannot use.
  2. 2. A UNIQUE column contains one row with email '[email protected]' and two rows with NULL. Is that valid?

    A unique index ignores NULLs when it checks for duplicates, so you can store any number of them.
  3. 3. What did MySQL do with a CHECK constraint before 8.0.16?

    Old versions accepted the syntax for compatibility but never evaluated it. Since 8.0.16 CHECK constraints are enforced.
  4. 4. A row has NULL in a column that has CHECK (price >= 0). What happens on INSERT?

    CHECK fails only when the condition is false. An unknown result (from NULL) passes, so add NOT NULL when a value is mandatory.

Summary

  • The primary key is also the clustered index in InnoDB: it orders the rows on disk and is copied into every secondary index, so keep it narrow.
  • Use sql_require_primary_key to forbid tables without one.
  • UNIQUE allows any number of NULL values, and its index is named after the column unless you name it.
  • CHECK is enforced since 8.0.16, passes on NULL, and must be deterministic. Older servers ignored it.
  • Name every constraint with a consistent prefix: error messages and ALTER TABLE ... DROP statements use the name.
  • INVISIBLE columns are hidden from SELECT * and from inserts without a column list, which makes schema changes safer.

Next: Foreign Keys in MySQL, which links tables together and keeps those links honest.