MySQL Primary Keys, Foreign Keys, and Constraints

Why database constraints matter

A database is often written by more than one code path: a web application, background job, import process, reporting task, or administrator. If important rules exist only in one form or endpoint, another writer can bypass them and create inconsistent data. MySQL constraints make core rules part of the schema, so the server validates them regardless of which client performs a write. Application validation remains useful for clear feedback, but it does not replace database integrity.

A primary key uniquely identifies each row and cannot contain NULL. A unique key enforces uniqueness for another identifier, such as a username or SKU. A foreign key requires a value in one table to refer to an existing key in another. NOT NULL requires a value to be present, and a CHECK constraint can enforce supported row-level conditions. Choose each rule from the domain rather than adding constraints without understanding their operational effect.

Primary and unique keys

CREATE TABLE customers (
  customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(254) NOT NULL,
  PRIMARY KEY (customer_id),
  UNIQUE KEY uq_customers_email (email)
) ENGINE = InnoDB;

The surrogate primary key provides a compact stable reference. The unique email constraint enforces a business rule separately. In MySQL, unique indexes permit multiple NULL values, so a nullable unique column does not mean there can only be one row without a value. If every record requires an email, pair uniqueness with NOT NULL. Also consider collation: values that differ only by case may compare equal under a case-insensitive collation.

Foreign keys and referential actions

Foreign keys protect relationships between parent and child rows. In an orders table, customer_id can reference the customer that placed the order. The key definitions must be compatible, and both tables should use a storage engine that supports the constraint. MySQL InnoDB is the ordinary choice. Indexes support foreign-key checking and join access; MySQL may create an index for a foreign-key column when needed, but explicit schema review still matters.

CREATE TABLE orders (
  order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  total DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (order_id),
  CONSTRAINT chk_orders_total CHECK (total >= 0),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
    ON DELETE RESTRICT
) ENGINE = InnoDB;

RESTRICT prevents deleting a customer while dependent orders exist. CASCADE can be appropriate for disposable child data, but it can also erase valuable records unexpectedly. SET NULL requires a nullable child column. Choose update and deletion actions based on ownership and retention requirements, not convenience. Always test parent deletion behavior before relying on it in production.

CHECK constraints and version awareness

A CHECK constraint validates a row expression, such as a nonnegative amount. MySQL 8.0.16 and later enforce CHECK constraints; earlier MySQL 8.0 releases parsed but ignored them. Do not assume enforcement from the presence of the constraint text in a schema dump. Verify the deployed server version and test invalid data. Also remember that a CHECK expression evaluating to UNKNOWN because of NULL is not the same as a NOT NULL rule, so combine constraints when both presence and a valid range are required.

Constraint names and schema changes

Use meaningful, predictable names for foreign keys, checks, and unique keys. Named constraints are easier to inspect in error messages and migrations than generated names. Constraint failures are part of application behavior: translate known validation errors into useful responses, but do not expose raw database details or treat every integrity failure as success. If a batch insert partially fails, understand the transaction and statement behavior before retrying.

Adding a constraint to an existing large table may scan data, acquire locks, or fail because old rows violate the new rule. Before deployment, find and repair invalid rows, test DDL on representative data, and plan for runtime and rollback limitations. MySQL DDL may implicitly commit, so do not assume every schema change can be wrapped in a transaction. See schema migrations and recovery planning.

Design for integrity and queries

Primary keys are often used in joins and secondary indexes, so their size and stability influence storage. Composite keys can express natural uniqueness for a relationship table, but their column order affects index access patterns. Keep foreign-key types aligned exactly and index common lookup paths based on measured query needs. Review schema behavior with SHOW CREATE TABLE and test invalid examples deliberately.

Keys and constraints define a reliable contract for stored data. Use them to encode identity, uniqueness, and relationships, then complement them with application validation, tested migrations, and least-privilege accounts. Continue with relationship patterns, normalization, and index design.