Primary Key / Foreign Key 🔑 — Database Relationships

Primary Key uniquely identifies a row in a table. A Foreign Key creates a relationship between two tables by referencing a key in another table.

erDiagram
    CUSTOMER ||--o{ ORDER : places

    CUSTOMER {
        int CustomerId PK
        string Name
    }

    ORDER {
        int OrderId PK
        int CustomerId FK
        decimal Amount
    }

1. Primary Key

A primary key must uniquely identify each record in a table.

CustomerId | Name
-----------|-------
1          | Rahul
2          | Priya
3          | Amit

Here, CustomerId is the primary key.

• Must uniquely identify a row
• Cannot contain duplicate values
• Normally cannot be NULL
• A table has one primary key constraint

2. Foreign Key

A foreign key references a key in another table.

erDiagram
    CUSTOMER ||--o{ ORDER : places

    CUSTOMER {
        int CustomerId PK
    }

    ORDER {
        int OrderId PK
        int CustomerId FK
    }

Order.CustomerId references Customer.CustomerId.

3. Referential Integrity

A foreign key helps maintain referential integrity. It prevents an order from referencing a customer that does not exist, subject to the database's configured constraints and operations.

flowchart LR
    O["Order.CustomerId"] --> C["Customer.CustomerId"]

    C -->|"Customer Exists"| V["Valid"]
    C -->|"Customer Missing"| X["Constraint Violation"]

4. Composite Primary Key

A primary key can contain more than one column. This is called a composite primary key.

erDiagram
    ORDER_ITEM {
        int OrderId PK
        int ProductId PK
        int Quantity
    }

Here, the combination of OrderId + ProductId uniquely identifies an order item.

5. Surrogate vs Natural Key

Surrogate keys are commonly used because business values can change while an internal identifier can remain stable.

6. Primary Key vs Foreign Key

7. Why Index Foreign Keys?

Foreign-key columns are frequently used in joins and relationship-based queries. Appropriate indexing can improve those queries and can also help database operations involving related rows, depending on the database and workload.

flowchart LR
    C["Customer"] --> O["Order"]
    O --> I["Index on CustomerId"]
    I --> Q["Faster Lookup / Join"]

Important Interview Questions

• What is a primary key?
• What is a foreign key?
• Primary key vs foreign key?
• Can a foreign key contain duplicate values?
• Can a foreign key be NULL?
• What is referential integrity?
• What is a composite primary key?
• Surrogate key vs natural key?
• Why might you index a foreign key?

Key Takeaways

• Primary Key → identifies a row.
• Foreign Key → connects related tables.
• Foreign keys help maintain referential integrity.
• Composite keys use multiple columns together.
• Appropriate indexes can improve relationship-based queries.

Conclusion

For interviews, remember: Primary Key = Identity and Foreign Key = Relationship.