Cheat Sheet logoCheat Sheet Today
MySQL DDL and Schema Design Cheatsheet/Create a table with a composite primary key

Use multiple columns as the logical primary key.

Back to sheetBrowse commands
Sign in to save
Section: constraints and keys

Create a table with a composite primary key

sql
sql
CREATE TABLE user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_code VARCHAR(50) NOT NULL,
  granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, role_code)
) ENGINE=InnoDB;
Explanation

Composite keys are common in mapping or junction tables.

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 commandsView fixes

Related commands

Same sheet · prioritizing constraints and keys
Add a unique key
Prevent duplicate values for a column or column pair.
OpenIn sheetsqlsame section
Create a table with a foreign key
Reference a parent table and enforce relational integrity.
OpenIn sheetsqlsame section
Drop a foreign key
Remove a foreign key constraint from a table.
OpenIn sheetsqlsame section
Drop a database
Delete an entire schema and all objects inside it.
OpenIn sheetsqlsame section
Create a database
Create a schema if it does not already exist.
OpenIn sheetsql
Create a users table
Create a table with an auto-increment primary key and timestamps.
OpenIn sheetsql