? MySQL PRIMARY KEY and FOREIGN KEY

? Quick Overview

The PRIMARY KEY and FOREIGN KEY are fundamental concepts in relational database design. They help ensure data integrity by defining relationships between tables. The PRIMARY KEY uniquely identifies each record in a table, while the FOREIGN KEY establishes a link between two tables.

? Key Concepts

• Primary keys uniquely identify rows
• Primary key values cannot be NULL
• Foreign keys reference primary keys
• Relationships enforce data consistency

? Syntax & Theory

Each table can have only one primary key, but that key may consist of multiple columns. Foreign keys ensure that related records always exist in the referenced table.

? Primary Key Example

// Creating a table with a PRIMARY KEY
CREATE TABLE employees (
employee_id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
PRIMARY KEY (employee_id)
);

? Foreign Key Example

// Creating a table with a FOREIGN KEY reference
CREATE TABLE orders (
order_id INT NOT NULL,
employee_id INT,
order_date DATE,
PRIMARY KEY (order_id),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

? Live Explanation

The employees table stores unique employee records. The orders table references employees using a foreign key, ensuring that every order is associated with a valid employee.

? Visual Relationship

? Use Cases

• Employees and departments
• Students and courses
• Orders and customers