MySQL DDL and Schema Design Cheatsheet/Create a table with a foreign key

Reference a parent table and enforce relational integrity.

Back to sheet Browse commands

Sign in to save

Section: constraints and keys

Create a table with a foreign key

sql

sql

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_orders_user_id (user_id),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE=InnoDB;

Explanation

The explicit index on the foreign key column helps join and lookup performance.

View in sheet →

Learn the surrounding workflow

Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.

Compare commands View fixes

Related commands

Same sheet · prioritizing constraints and keys

Drop a foreign key

Remove a foreign key constraint from a table.

Open In sheetsqlsame section

Add a unique key

Prevent duplicate values for a column or column pair.

Open In sheetsqlsame section

Create a table with a composite primary key

Use multiple columns as the logical primary key.

Open In sheetsqlsame section

Drop a database

Delete an entire schema and all objects inside it.

Open In sheetsqlsame section

Create a database

Create a schema if it does not already exist.

Open In sheetsql

Create a users table

Create a table with an auto-increment primary key and timestamps.

Open In sheetsql