🔐 MySQL Keys – UNIQUE, PRIMARY KEY, FOREIGN KEY Explained

🧲 Introduction – Why Use Keys in MySQL?

In MySQL, keys are a fundamental part of relational database design. They ensure data uniqueness, row identification, and relationships across tables. Using keys properly allows you to:

• Prevent duplicate entries (UNIQUE)
• Identify rows with certainty (PRIMARY KEY)
• Maintain referential integrity (FOREIGN KEY)

🎯 In this guide, you’ll learn:

• The role and syntax of PRIMARY KEY, FOREIGN KEY, and UNIQUE
• How to define and manage keys
• Real-world use cases and best practices

🔑 1. PRIMARY KEY – Uniquely Identify Rows

A PRIMARY KEY is a column (or set of columns) that uniquely identifies each row in a table.

🔹 Syntax

PRIMARY KEY (column1 [, column2])

🔹 Example

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100)
);

Explanation:

• emp_id must be unique and not null.
• Automatically indexed.

🔹 Composite Primary Key

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

Explanation:
Prevents a student from enrolling in the same course more than once.

✅ 2. UNIQUE – Enforce Column Uniqueness

The UNIQUE constraint ensures that all values in a column (or set of columns) are distinct.

🔹 Syntax

UNIQUE (column_name)

🔹 Example

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

Explanation:

• Prevents duplicate emails from being stored.
• Allows one NULL (depending on configuration).

🔹 Multi-Column Unique

UNIQUE (first_name, last_name)

Explanation:
Ensures the same combination of first_name and last_name doesn’t appear more than once.

🔗 3. FOREIGN KEY – Enforce Relationships

A FOREIGN KEY creates a link between two tables, ensuring that data in the child table matches existing values in the parent table.

🔹 Syntax

FOREIGN KEY (child_column) REFERENCES parent_table(parent_column)
[ON DELETE action] [ON UPDATE action]

🔹 Example

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Explanation:
Ensures you can’t insert an order for a non-existent customer.

🔹 With Cascading Actions

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE

• ON DELETE CASCADE: Deletes child rows if the parent is deleted.
• ON UPDATE CASCADE: Updates child keys if the parent changes.

📋 Key Differences Summary

🧪 Add Keys to Existing Tables

🔹 Add Primary Key

ALTER TABLE employees ADD PRIMARY KEY (emp_id);

🔹 Add Unique Key

ALTER TABLE users ADD UNIQUE (email);

🔹 Add Foreign Key

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

⚠️ Dropping Keys

🔹 Drop Primary Key

ALTER TABLE employees DROP PRIMARY KEY;

🔹 Drop Unique Key

ALTER TABLE users DROP INDEX email;

🔹 Drop Foreign Key

ALTER TABLE orders DROP FOREIGN KEY fk_customer;

📘 Best Practices

🚀 Real-World Use Cases

📌 Summary – Recap & Next Steps

MySQL keys are essential to relational data modeling. They ensure each row is unique, searchable, and correctly related to other data. Understanding and applying PRIMARY, UNIQUE, and FOREIGN keys allows you to build robust and scalable schemas.

🔍 Key Takeaways

• Use PRIMARY KEY to uniquely identify rows
• Use UNIQUE to prevent duplicates in critical columns
• Use FOREIGN KEY to connect related tables safely
• Cascade actions with care
• Index keys for performance

⚙️ Real-World Relevance

Keys are foundational in e-commerce, CRM, HR systems, finance, and any relational data system requiring clean, valid, and interconnected records.

❓ FAQ – MySQL Keys

❓ Can I have multiple UNIQUE constraints?

✅ Yes. You can have multiple UNIQUE constraints on different columns or combinations.

❓ Can a FOREIGN KEY reference a non-primary column?

✅ Yes, but the referenced column must have a UNIQUE or PRIMARY KEY constraint.

❓ Can a PRIMARY KEY be null?

❌ No. PRIMARY KEY is always NOT NULL.

❓ How do I create a named constraint?

CONSTRAINT constraint_name UNIQUE (column_name)

❓ Do FOREIGN KEYS improve performance?

✅ They improve data integrity, but may add overhead on INSERT/DELETE. Always index both sides for best JOIN performance.