Difference Between Primary Key and Foreign Key

Primary keys and foreign keys are essential concepts in relational databases. They help maintain data integrity and define relationships between tables. Understanding their differences is crucial for database design.

What is a Primary Key?

A primary key is a column (or set of columns) that uniquely identifies each record in a table. It cannot contain NULL values and must be unique.

-- Primary key example
CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

What is a Foreign Key?

A foreign key is a column that creates a link between two tables. It refers to the primary key of another table and helps maintain referential integrity.

-- Foreign key example
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(id)
);

Key Differences Between Primary Key and Foreign Key

• Primary key uniquely identifies records, foreign key links tables
• Primary key cannot be NULL, foreign key can be NULL
• Each table has one primary key, but can have multiple foreign keys
• Primary key ensures entity integrity, foreign key ensures referential integrity
• Primary key values must be unique, foreign key values can repeat

Comparison Table

Example Relationship

-- Linking tables
SELECT * FROM Students s
JOIN Orders o ON s.id = o.student_id;

When to Use Primary Key?

• To uniquely identify records
• For indexing and fast lookup
• To enforce uniqueness
• In every table design

When to Use Foreign Key?

• To establish relationships
• To enforce referential integrity
• To link related data
• In relational database design

Real-World Applications

• Primary key in user IDs
• Foreign key in order systems
• Primary key in product tables
• Foreign key in transaction logs
• Both used in relational databases

Common Mistakes to Avoid

• Using non-unique primary keys
• Forgetting foreign key constraints
• Mismatched data types
• Ignoring indexing
• Improper relationship design

Advanced Concepts

• Composite keys
• Cascade operations
• Indexing strategies
• Normalization
• ON DELETE / ON UPDATE rules

Practice Exercises

• Create tables with keys
• Establish relationships
• Perform joins
• Test referential integrity
• Design normalized schema

Conclusion

Primary keys and foreign keys are fundamental for database design. Primary keys ensure uniqueness, while foreign keys maintain relationships. Both are essential for building reliable and efficient databases.