Composite Key

Hook: Interviewers love this because a composite key checks whether you know how to identify a row by a pair of columns, not just a single ID.

Question: What is a composite key in SQL?

Answer: A composite key is a key made from two or more columns that together uniquely identify a row. One column by itself is not enough; the combination is what matters. In SQL, you usually enforce it with a composite PRIMARY KEY or UNIQUE constraint.

Interview-Ready Answer: A composite key is a row identifier made from more than one column. I use it when no single column is unique enough, like order_id plus product_id in an order line table. In SQL, I would enforce that with a composite primary key so the database prevents duplicate pairs and can index the pair for fast lookups.

What it is

Detailed Explanation: A composite key means the database treats multiple columns as one logical identifier. Think of it as an address made from several parts: each part helps, but only the full combination points to one row. Most interview questions are really testing whether you understand that the uniqueness rule applies to the set of columns, not each column alone.

How it works under the hood

1. The database creates a unique constraint, often backed by a B-tree index. A B-tree is a sorted tree structure that lets the engine find rows quickly.
2. When you insert a row, the engine compares the full column pair, in order, against existing entries. If the same combination already exists, the insert is rejected.
3. For a composite PRIMARY KEY, every column is also treated as NOT NULL, so the key must be fully present.
4. When you query by the leftmost column(s), the index is usually useful. If the key is (order_id, product_id), then WHERE order_id = 1001 can use the index well, but WHERE product_id = 501 usually cannot use it efficiently by itself.
5. On updates, the database must keep the index in sync. That is why wider keys cost more storage and slightly more write work.

When and why to use it

Use a composite key when the real-world identity naturally comes from more than one value: line items in an order, a student in a course, or language-specific translations. It is especially strong in junction tables, which are tables that connect two other tables in many-to-many relationships.

A surrogate key is a made-up ID with no business meaning. It is often easier to reference from many tables, but it adds another column and does not prevent duplicate business data unless you also add a composite UNIQUE constraint.

Performance and complexity notes

• Lookup and insert cost with the index are typically O(log n), because the engine searches a tree, not the whole table.
• The key comparison itself is slightly more expensive than a single column because the database may compare two or more fields.
• Wide text columns make indexes bigger. Two INT columns might be around 8 bytes of raw data, while two long VARCHAR values can expand the index to hundreds of bytes per entry.
• Larger indexes mean fewer entries per page, which reduces fan-out (the number of children a tree node can point to) and can increase tree depth.

Important edge cases

• Column order matters in the index. Put the column you search by most often first.
• A composite PRIMARY KEY cannot contain NULL values.
• A composite UNIQUE constraint is similar, but some databases treat NULL values differently there.
• If a key column can change, the whole key can become painful to update because every referencing foreign key may need to move too.

Memory Hook: Think of a concert seat: section + row + seat. Any one part alone is incomplete, but the full combination finds exactly one seat.

Real-World Story: In a checkout service, each order can contain many products. The table that stores line items often uses (order_id, product_id) as the composite key, because one order may contain product 501 and product 502, but the same product should not be duplicated in the same order line table.

Imagine the team accidentally uses only order_id as the primary key. The first item inserts fine, but the second item for the same order fails with a duplicate-key error. In production, that shows up as missing items in receipts, incorrect totals, and logs like duplicate key value violates unique constraint. If the app retries blindly, customers may see half-built carts or shipment rows that point to the wrong line item.

The same idea helps in shipping. A shipment table may need to reference both the order and the specific product line, so a composite foreign key prevents orphan shipment rows from pointing at a line item that does not exist. That is the practical value: the database protects the business rule for you.

Follow-up & Tricky Questions:

• Why not use a surrogate key instead? A surrogate key is easier to reference and can keep indexes smaller, but you still need a composite UNIQUE constraint if the business rule depends on multiple columns.
• Can a foreign key reference a composite primary key? Yes. The child table must store all referenced columns in the same order, and the values must match an existing parent row.
• Does column order matter in a composite key? Yes. It affects both index usefulness and which queries can use the leftmost part of the index efficiently.
• Can a composite key contain NULL? Not if it is a primary key. Primary key columns are implicitly NOT NULL.
• Is a composite key the same as a composite unique constraint? Close, but not identical. A composite primary key is the chosen row identifier; a composite unique constraint only enforces uniqueness.
• Tricky: If two rows differ in one column, are they duplicates? No. For a composite key, the full combination must match before the database says it is a duplicate.
• Tricky: Can you search efficiently by the second column alone? Usually not with a B-tree index on (col1, col2). The engine typically needs the leftmost column to use that index well.
• Tricky: Does a composite key always mean better design? No. If the key columns are wide or likely to change, a surrogate key plus a composite unique constraint may be easier to maintain.

Common Mistakes:

• Confusing composite key with multiple primary keys. Correction: a table has one primary key constraint, but that primary key can span multiple columns.
• Forgetting the index order matters. Correction: put the most common lookup column first, or your index may not help the queries you run most.
• Using mutable business fields as keys. Correction: if the values change often, the database and all foreign keys have extra work.
• Assuming a composite key replaces all other uniqueness rules. Correction: sometimes you still need extra UNIQUE constraints for other business rules.

Memory Hook: section + row + seat: one part alone is not enough, but the full combination points to exactly one place.

Cheat Sheet:

• Composite key = two or more columns identify one row.
• Primary key version means all columns are NOT NULL.
• Best for junction tables and line-item tables.
• Column order matters for index usage.
• Surrogate key is simpler to reference; composite key is more natural to the business rule.
• Composite foreign keys can reference composite primary keys.

Practice Tasks:

• Build a student_courses table using (student_id, course_id) as the primary key.
• Add a child table that references that composite key with a foreign key.
• Try inserting a duplicate pair and confirm the database blocks it.