What is the difference between primary key and foreign key

Short answer

A primary key uniquely identifies each row in a table. A foreign key establishes a link between two tables by referencing the primary key of another table.

Key differences

• Purpose: Primary keys ensure row uniqueness within a table. Foreign keys maintain referential integrity across tables.
• Nullability: Primary keys cannot be null. Foreign keys can usually be null to indicate optional relationships.
• Quantity: A table has one primary key but can have multiple foreign keys.
• Indexing: Primary keys are automatically indexed. Foreign keys should be indexed for join performance.

When to use each

• Define a primary key on every table for identification and clustering.
• Add foreign keys whenever a column logically references another table to enforce cascading rules and prevent orphaned rows.r