MySql - Understanding primary keys and foreign keys

Understanding Primary Keys and Foreign Keys in MySQL

In relational databases like MySQL, maintaining data integrity and establishing relationships between tables are core principles of database design. Two key concepts used to enforce these principles are primary keys and foreign keys. Understanding these concepts is essential for anyone working with databases, whether you're designing a database schema, building applications, or maintaining existing systems.

1. What is a Primary Key?

1.1 Definition of a Primary Key

A primary key is a column (or a combination of columns) in a database table that uniquely identifies each row in that table. The values in the primary key column(s) must be unique and cannot be NULL.

1.2 Characteristics of Primary Keys

• Uniquely identifies each record
• Must contain unique values
• Cannot contain NULLs
• Each table can have only one primary key
• Can be composed of one or more columns (composite key)

1.3 Importance of Primary Keys

Primary keys play a vital role in data integrity and relational database design:

• They prevent duplicate records
• Serve as a reference point for foreign keys
• Enable fast indexing and access to data
• Form the basis of entity relationships

2. Creating Primary Keys in MySQL

2.1 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)
);
  

2.2 Auto Incrementing Primary Keys

It’s common to use the AUTO_INCREMENT attribute with integer primary keys:

CREATE TABLE departments (
  dept_id INT AUTO_INCREMENT,
  dept_name VARCHAR(100) NOT NULL,
  PRIMARY KEY (dept_id)
);
  

2.3 Composite Primary Keys

A composite primary key consists of two or more columns:

CREATE TABLE course_enrollments (
  student_id INT,
  course_id INT,
  enrollment_date DATE,
  PRIMARY KEY (student_id, course_id)
);
  

2.4 Adding Primary Key to Existing Table

ALTER TABLE products
ADD PRIMARY KEY (product_id);
  

2.5 Removing a Primary Key

ALTER TABLE employees
DROP PRIMARY KEY;
  

3. What is a Foreign Key?

3.1 Definition of a Foreign Key

A foreign key is a column (or group of columns) in one table that refers to the primary key in another table. Foreign keys establish relationships between tables and enforce referential integrity.

3.2 Characteristics of Foreign Keys

• Used to link two tables together
• Enforces referential integrity between parent and child tables
• Can allow NULLs if not defined as NOT NULL
• Can be defined on a single or multiple columns

3.3 Importance of Foreign Keys

• Maintains consistency across related tables
• Prevents orphaned records
• Enforces rules on updates and deletions using ON DELETE and ON UPDATE

4. Creating Foreign Keys in MySQL

4.1 Syntax for Creating Foreign Key

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

4.2 Foreign Key with Constraints

CREATE TABLE payments (
  payment_id INT AUTO_INCREMENT,
  order_id INT,
  amount DECIMAL(10,2),
  PRIMARY KEY (payment_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
  

4.3 Adding Foreign Key to Existing Table

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

4.4 Dropping Foreign Keys

ALTER TABLE orders
DROP FOREIGN KEY fk_customer;
  

5. Referential Actions: ON DELETE and ON UPDATE

5.1 CASCADE

Automatically deletes or updates child rows when the parent row is deleted or updated.

5.2 SET NULL

Sets the foreign key column to NULL when the referenced row is deleted or updated.

5.3 NO ACTION and RESTRICT

Prevents deletion or update of a referenced row if it has dependent rows.

5.4 Examples

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE SET NULL
  ON UPDATE CASCADE;
  

6. Best Practices for Primary and Foreign Keys

6.1 Use Natural vs. Surrogate Keys

• Natural Key: Uses actual data (e.g., SSN, email)
• Surrogate Key: Uses system-generated value (e.g., auto-increment ID)

6.2 Index Foreign Keys

Indexing foreign keys improves join performance and referential checks.

6.3 Always Define Foreign Keys Explicitly

Even though MySQL allows tables without foreign keys, defining them helps prevent inconsistent data.

6.4 Use InnoDB Storage Engine

Only InnoDB supports foreign keys. Avoid MyISAM for relational integrity.

7. Common Errors and Troubleshooting

7.1 Cannot Create Foreign Key Constraint

• Check that the referenced column is a primary or unique key
• Ensure data types match exactly
• Check that both tables use InnoDB

7.2 Duplicate Entry Errors

Happens when inserting a row that violates a primary key or unique constraint.

7.3 Orphan Records

Occurs when a child record exists without a matching parent. Prevented using foreign key constraints.

8. Real-World Use Cases

8.1 E-commerce Application

Products, customers, orders, and payments all have primary keys and are interlinked through foreign keys to enforce consistency and logical relationships.

8.2 School Management System

• students: student_id (PK)
• courses: course_id (PK)
• enrollments: student_id (FK), course_id (FK)

8.3 Hospital Database

• doctors: doctor_id (PK)
• patients: patient_id (PK)
• appointments: appointment_id (PK), doctor_id (FK), patient_id (FK)

9. Viewing Keys in Information Schema

9.1 Querying Primary Keys

SELECT table_name, column_name
FROM information_schema.key_column_usage
WHERE constraint_name = 'PRIMARY' AND table_schema = 'your_database';
  

9.2 Querying Foreign Keys

SELECT table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL
  AND table_schema = 'your_database';
  

Primary and foreign keys are foundational to relational database design. Primary keys uniquely identify records, ensuring data uniqueness. Foreign keys establish logical relationships and ensure referential integrity between related tables. Together, they enable robust and scalable applications by maintaining consistency and structure in your database.

When designing any MySQL database, understanding and properly implementing primary and foreign keys helps prevent data anomalies, enforce business rules, and optimize data retrieval operations. Mastering these concepts is an essential skill for developers, database administrators, and data architects alike.