MySQL Types of Keys

Introduction

In MySQL, keys play a vital role in organizing and indexing data within a table. They ensure data integrity, enforce relationships between tables, and improve query performance. In this article, we will explore the different types of keys in MySQL and provide examples to illustrate their usage.

Primary Key

A primary key uniquely identifies each record in a table. It must have a unique value for each row and cannot contain null (empty) values. Here's an example of defining a primary key:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

Foreign Key

A foreign key establishes a relationship between two tables by referencing the primary key of another table. It ensures referential integrity and maintains the relationship between related tables. Here's an example:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  product_id INT,
  customer_id INT,
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Unique Key

A unique key ensures that each value in a column or a group of columns is unique. Unlike the primary key, it can contain null (empty) values, but the non-null values must be unique. Here's an example:

CREATE TABLE products (
  id INT PRIMARY KEY,
  code VARCHAR(10) UNIQUE,
  name VARCHAR(50)
);

Composite Key

A composite key consists of multiple columns that together uniquely identify each record in a table. This is useful when a single column does not provide a unique identifier. Here's an example:

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  item_number INT,
  PRIMARY KEY (order_id, item_number),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Conclusion

Understanding the different types of keys in MySQL is essential for designing effective database schemas and ensuring data integrity. In this article, we explored the primary key, foreign key, unique key, and composite key in MySQL, providing examples to illustrate their usage. By using the appropriate key types, you can establish relationships between tables, enforce data uniqueness, and optimize query performance in your MySQL databases.