Keys in MySQL are used to enforce data integrity and define relationships between tables. They ensure uniqueness, prevent duplicate data, and maintain consistency across related tables.

1. PRIMARY KEY – Uniquely Identify Each Row

A primary key uniquely identifies each row and cannot be NULL. Each table can have only one primary key.

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  PRIMARY KEY (column_name)
);
Example:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name VARCHAR(100)
);

2. UNIQUE – Enforce Uniqueness (Without Being Primary)

Ensures that values in a column (or group of columns) are not duplicated, but can allow NULLs.

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype UNIQUE
);
Example:

CREATE TABLE employees (
  id INT,
  email VARCHAR(100) UNIQUE
);

3. FOREIGN KEY – Link Between Tables

A foreign key connects a column in one table to the primary key in another. It enforces referential integrity.

CREATE TABLE child_table (
  column datatype,
  FOREIGN KEY (column) REFERENCES parent_table(parent_column)
);
Example:

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES users(user_id)
);

4. COMPOSITE KEY – Combine Multiple Columns as Key

A composite key uses two or more columns together to uniquely identify a row.

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  PRIMARY KEY (column1, column2)
);
Example:

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);
PreviousNext