SQL Database Design and Foreign Keys with Examples

By Ravindra Bagale, trainer · LinkedIn · Last updated: 18 October 2026

1. Practice data

Why: every command below shows its result. If you start from the same empty database, you get the same answers and can check yourself.

How:

1. Log in with mysql -u root -p.

2. Start with a fresh instagram database. If you still have one from an earlier SQL chapter, this removes it:

   DROP DATABASE IF EXISTS instagram;
   CREATE DATABASE instagram;
   USE instagram;
   

3. This time we don't create the table and fix it later. The keys go in right away, in the next section.

2. Set the keys while you create the table

Why: in the last SQL chapter we added keys later with ALTER TABLE. That works, but if duplicates have already crept in, the ALTER TABLE fails and you must clean the data first. In real projects the keys are decided before the first row goes in.

What: a key can be written in two places inside CREATE TABLE:

1. Inline, on the column itself: email VARCHAR(50) UNIQUE.
2. Table level, as a separate item after all the columns: UNIQUE(email).
3. Both give exactly the same table. DESC shows UNI on email either way.
4. The table-level form is just one more item in the list, so it needs a comma before it, like every column.

How:

1. Create users with the table-level form:

   CREATE TABLE users (
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(20),
     email VARCHAR(50),
     UNIQUE(email)
   );
   

2. DESC users; shows id as PRI with auto_increment, and email as UNI.

3. The inline form would be email VARCHAR(50) UNIQUE with no last line. Don't run it now; users already exists.
4. Leave out the comma before UNIQUE(email) and MariaDB stops with ERROR 1064 (42000): You have an error in your SQL syntax, pointing at (email).
5. Use the table-level form when a rule covers more than one column, or when you like all the rules together at the end. Otherwise inline is shorter.

3. UNIQUE at work: ERROR 1062 and the skipped id

Why: one email must belong to one account. Login, password reset and "this email is already registered" all depend on it.

How:

1. Add a user. There is no id in the list, so AUTO_INCREMENT picks it:

   INSERT INTO users (name, email) VALUES ('amit', 'amit@example.com');
   

2. Query OK, 1 row affected. amit gets id 1.

3. Try the same email again:

   INSERT INTO users (name, email) VALUES ('amit', 'amit@example.com');
   

4. MariaDB refuses: ERROR 1062 (23000): Duplicate entry 'amit@example.com' for key 'email'. The key is named after the column.

5. Add two more users:

   INSERT INTO users (name, email) VALUES ('neha', 'neha@example.com');
   INSERT INTO users (name, email) VALUES ('rahul', 'rahul@example.com');
   

6. SELECT * FROM users; shows ids 1, 3, 4. The failed insert used up id 2, as you saw with AUTO_INCREMENT gaps in the last SQL chapter.

4. What database design is

Why: an app is only as good as its tables. A bad design works for a week and then cannot answer simple questions, like "who liked this reel?"

What: database design means deciding:

1. How many tables the app needs.
2. Which columns go in which table.
3. How the tables are linked to each other.

How: design comes before the first CREATE TABLE, usually on paper:

1. List what the app shows on each screen.
2. Group the data by "thing": a person, a reel, a like, an order.
3. Each kind of thing becomes a table; each single fact about it becomes a column.
4. Try one sample row per table and check every cell (section 7).

5. Suppose we are building Instagram Reels

Why: a reel screen looks simple: a profile photo, a username, the video, a caption, and counts for likes and comments. Each of those must come from a table.

What: the first idea most people have is one reels table with everything in it:

1. id, user_id (who uploaded it), caption, video_url.
2. Plus counters: likes, comments, shares, each a plain number.

How: test the design with real questions.

1. Reel 102 has likes = 3. Someone taps like; the app changes it to 4. Fine so far.
2. Who liked it? The counter can't say. It is only a number.
3. When did they like it? Not stored either.
4. Did the same person like it twice? There is no way to check.
5. Someone taps unlike. Whose like should be removed?
6. Comments have the same problem, and worse: a comment also has text.

6. The likes table, and COUNT(*)

Why: the counter could not answer "who" or "when". Each like is its own event, so each like gets its own row.

What: a likes table with:

1. id: the like's own number.
2. user_id: who liked.
3. reel_id: which reel.
4. liked_at: when. DEFAULT CURRENT_TIMESTAMP fills in the current date and time by itself.

The number of likes is not stored anywhere. It is counted from the rows whenever it is needed.

How:

1. Create the table:

   CREATE TABLE likes (id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, reel_id INT,
     liked_at DATETIME DEFAULT CURRENT_TIMESTAMP);
   

2. Three users like reel 102, and amit also likes reel 105:

   INSERT INTO likes (user_id, reel_id) VALUES (1, 102), (3, 102), (4, 102), (1, 105);
   

3. Query OK, 4 rows affected. Each row got today's date and time in liked_at.

4. Count the likes on reel 102 from the likes table:

   SELECT COUNT(*) FROM likes WHERE reel_id = 102;
   

5. The answer is 3.

6. Tap the count, and the app lists who liked it:

   SELECT user_id, liked_at FROM likes WHERE reel_id = 102;
   

7. You get users 1, 3 and 4, each with a time. The app then shows their names and profile photos from users.

8. Unlike is now simple: delete that one row, for example DELETE FROM likes WHERE user_id = 4 AND reel_id = 102;
9. The reels table keeps only what a reel has once: who uploaded it, the caption, the video link.

7. The rule: many values per row, own table

Why: you need a quick test you can apply to any column of any app.

What:

1. One value per row: keep it as a column. A reel has one caption, one video link, one uploader.
2. Many values per row, or a list that keeps growing: give it its own table, with one row per value. A reel has many likes, many comments, many tags.
3. Never squeeze a list into one cell, like '1,3,4'. You could not count, search or remove one of them cleanly.

How: the paper test.

1. Write the columns of a table on paper.
2. Fill in one sample row with realistic values.
3. Look at each cell. If it needs a comma-separated list, that data needs its own table.
4. Example: caption = 'chai time' is one value; liked by = 1, 3, 4, ... is many, so likes is a table.
5. Example: tags = chai, rain, pune is many, so tags go in a reel_tags table: one row per reel and tag.

8. Suppose we are building Flipkart: walk the user journey

Why: a shopping app has dozens of tables. Guessing them at random misses some; walking through the app like a customer finds them in order.

How: follow one customer and note what must be stored at each step.

1. Register and profile: a users table, one row per person.
2. Delivery address: an addresses table. One user can have many addresses: you live in Pune but send a gift to your parents in another city. Many values per user means its own table.
3. Browse: categories (mobiles, shoes, books) and products.
4. Cart and wishlist: cart (user, product, quantity) and wishlist (user, product).
5. Pay: orders (who bought, how much) and payments (one or more per order).
6. After the order: order_tracking (one row per status change), returns, ratings.
7. Behind the scenes: sellers, offers, support_tickets.

What is not a table or a column: search, filter, sort, "add to cart", "buy now". These are actions. An action either reads rows (a SELECT with WHERE and ORDER BY) or adds a row (add to cart is an INSERT into cart).

9. Designing products: column or own table?

Why: products is where people put too much. Apply the rule from section 7 to every idea.

What:

1. Columns, one value per product: id, name, brand, price DECIMAL(10,2), description, return_days.
2. Size is three numbers, so store three columns: length_cm, width_cm, depth_cm. Then you can filter on each one.
3. Category: don't type "Mobiles" into every product row. Keep a categories table (id, name) and store only the number in products: category_id. A spelling fix then happens in one row.
4. Own tables, because there are many values or they change often:
   1. product_photos: many photos per product.
   2. ratings: many users rate one product.
   3. stock: changes with every sale.
   4. price_history: old prices with their dates.
   5. sellers: one product can be sold by many sellers.
   6. offers: they come and go.
5. Not columns at all: "good quality" is an opinion; it lives in ratings. "Add to cart" is an action.

10. Why foreign keys: orphan rows

Why: tables now hold each other's ids: likes.user_id, orders.user_id, products.category_id. Nothing so far checks that those ids are real.

What can go wrong:

1. users has ids 1, 3 and 4.
2. A reels row says reel 103 was uploaded by user 30. There is no user 30. Whose profile photo should the app show? This is an orphan row: a child with no parent.
3. An orders row says order 2, Rs 1,00,000, was placed by user 10, who does not exist. In an audit, nobody can say who paid that money.
4. Likes from users who don't exist make every like count wrong.

What: a foreign key is a rule on a column of one table: "every value here must exist in that column of the other table". The database checks it on every insert, update and delete.

11. Parent and child

Why: Every foreign key has two sides, and every error message in this chapter names one of them. Knowing which table is the parent tells you which row to fix.

What:

1. The table that is pointed to is the parent: users.
2. The table that holds the pointer is the child: orders, with its user_id column.
3. One parent row can have many children: one user, many orders.

The rules:

1. A value in the child's foreign key column must exist in the parent column. NULL is allowed unless you make the column NOT NULL.
2. The parent column must be a PRIMARY KEY or UNIQUE column. Usually it is the parent's primary key, users(id). A unique column such as users(email) works too.
3. Both columns must have the same type: INT to INT.
4. Create the parent table first. The child refers to it by name.

How: see what happens when a rule is broken. Just read these two examples; they fail on purpose.

1. A parent column without a key:

   CREATE TABLE bad (id INT PRIMARY KEY, uname VARCHAR(20), FOREIGN KEY (uname) REFERENCES users(name));
   

2. It fails: ERROR 1005 (HY000): Can't create table `instagram`.`bad` (errno: 150 "Foreign key constraint is incorrectly formed"). name has no key, so it can't be a parent column.

3. The same ERROR 1005 appears when the types differ (VARCHAR pointing to INT), or when the parent table doesn't exist yet.

12. CREATE TABLE with a FOREIGN KEY

Why: Writing the foreign key into CREATE TABLE puts the rule in the database from the first row, so no orphan can ever slip in.

How:

1. Create the child table. The foreign key is written at table level, after a comma, like UNIQUE(email):

   CREATE TABLE orders (
     id INT PRIMARY KEY AUTO_INCREMENT,
     user_id INT,
     product_id INT,
     amount DECIMAL(10,2),
     FOREIGN KEY (user_id) REFERENCES users(id)
   );
   

2. Read it as: "user_id in this table refers to id in users".

3. amount DECIMAL(10,2) stores money exactly: up to 10 digits, 2 of them after the point, such as 1299.50.
4. DESC orders; shows MUL in the Key column of user_id.
5. MUL means "multiple": the column has an index, and the same value may repeat. MariaDB creates this index for every foreign key. It makes sense: one user can have many orders.
6. Compare: PRI is the primary key, UNI is unique, MUL allows repeats.

13. ERROR 1452: a child needs a parent

Why: this is the whole point of the foreign key: no order for a user who doesn't exist.

How:

1. An order for rahul (id 4), who exists:

   INSERT INTO orders (user_id, product_id, amount) VALUES (4, 1001, 499.00);
   

2. Query OK, 1 row affected. It gets id 1.

3. The Rs 1,00,000 order for user 30, who doesn't exist:

   INSERT INTO orders (user_id, product_id, amount) VALUES (30, 1001, 100000.00);
   

4. MariaDB refuses: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`instagram`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)).

5. Read the message from the end: the constraint orders_ibfk_1 says user_id must match users.id, and 30 doesn't.
6. orders_ibfk_1 is the name MariaDB gave the foreign key, because we didn't name it.

7. An order for amit (id 1):

   INSERT INTO orders (user_id, product_id, amount) VALUES (1, 1002, 1299.50);
   

8. It gets id 3, not 2. The failed insert used up id 2, just like the duplicate email in section 3.

9. SELECT * FROM orders; shows order 1 (user 4, 499.00) and order 3 (user 1, 1299.50).

14. ERROR 1451: delete the child first

Why: the rule works in both directions. Deleting a parent that still has children would turn those children into orphans, so the database refuses that too.

How:

1. Try to delete amit, who has order 3:

   DELETE FROM users WHERE id = 1;
   

2. MariaDB refuses: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`instagram`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)).

3. Delete the child first:

   DELETE FROM orders WHERE id = 3;
   

4. Now the parent can go:

   DELETE FROM users WHERE id = 1;
   

5. Both answer Query OK, 1 row affected. SELECT * FROM users; shows neha and rahul.

6. The same protection covers changing a parent id (UPDATE users SET id = 33 WHERE id = 4; gives ERROR 1451) and dropping the whole parent table (DROP TABLE users; gives ERROR 1451 while orders refers to it).
7. To remove both tables, drop the child first: DROP TABLE orders; then DROP TABLE users;

15. Soft delete with is_active

Why: a customer closes the account. Deleting the user is blocked while orders exist, and deleting the orders would destroy bills, tax records and returns. Real apps almost never delete such rows.

What: a soft delete keeps the row and marks it inactive with a flag column, usually is_active: 1 for active, 0 for deactivated. The app shows only active rows.

How:

1. Add the flag. Every existing user starts as active:

   ALTER TABLE users ADD COLUMN is_active TINYINT NOT NULL DEFAULT 1;
   

2. rahul (id 4) closes his account:

   UPDATE users SET is_active = 0 WHERE id = 4;
   

3. The app lists only active users:

   SELECT * FROM users WHERE is_active = 1;
   

4. Only neha appears. rahul's row is still there, and so is his order 1: SELECT * FROM orders; still shows it, pointing to a real user.

5. rahul comes back? UPDATE users SET is_active = 1 WHERE id = 4; and everything is as before.
6. Remember to add WHERE is_active = 1 to every query that lists users in the app.

When you're done practising, type exit; and stop the EC2 instance.

16. Hands-on lab: keys, a foreign key and a soft delete

Why this lab. Keys are the database's own rules. This lab triggers each rule on purpose (duplicate email, order for a missing user, deleting a user with orders) and ends with the soft delete real apps use.

1. Create a fresh database
   • Command: CREATE DATABASE shop; USE shop;
   • You should see: Query OK, 1 row affected, then Database changed.
   • Check: The prompt shows [shop].
   • Why: A fresh database means your results match these steps exactly.
2. Create users with keys built in
   • Command: CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), email VARCHAR(50) UNIQUE);
   • You should see: Query OK, 0 rows affected
   • Check: DESC users; shows PRI on id and UNI on email.
   • Why: Setting keys in CREATE TABLE means the rules apply from the first row. Adding them later fails if duplicates have crept in.
3. Add two users
   • Command: INSERT INTO users (name, email) VALUES ('amit', 'amit@example.com'), ('neha', 'neha@example.com');
   • You should see: Query OK, 2 rows affected
   • Check: SELECT * FROM users; → ids 1 and 2.
   • Why: No ids typed: AUTO_INCREMENT gives 1 and 2.
4. Try a duplicate email
   • Command: INSERT INTO users (name, email) VALUES ('amit2', 'amit@example.com');
   • You should see: ERROR 1062 (23000): Duplicate entry 'amit@example.com' for key 'email'
   • Check: SELECT COUNT(*) FROM users; → 2.
   • Why: One email must belong to one account, or login and password reset break. UNIQUE enforces it.
5. Create orders with a foreign key
   • Command: CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, amount DECIMAL(10,2), FOREIGN KEY (user_id) REFERENCES users(id));
   • You should see: Query OK, 0 rows affected
   • Check: DESC orders; shows MUL on user_id.
   • Why: orders is the child; users is the parent. Both columns are INT, which a foreign key requires.
6. Add a valid order
   • Command: INSERT INTO orders (user_id, amount) VALUES (1, 499.00);
   • You should see: Query OK, 1 row affected
   • Check: SELECT * FROM orders; → 1 | 1 | 499.00.
   • Why: User 1 exists, so the child row is allowed.
7. Try an order for a missing user
   • Command: INSERT INTO orders (user_id, amount) VALUES (30, 100.00);
   • You should see: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...
   • Check: SELECT COUNT(*) FROM orders; → 1.
   • Why: No user 30, so no order for user 30. Without the foreign key this orphan row would sit in your sales data with nobody to bill.
8. Try to delete a user with orders
   • Command: DELETE FROM users WHERE id = 1;
   • You should see: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ...
   • Check: SELECT name FROM users; still shows amit.
   • Why: The rule works both ways: a parent with children can't vanish and leave orphans behind.
9. Soft delete instead
   • Command: ALTER TABLE users ADD COLUMN is_active TINYINT NOT NULL DEFAULT 1; UPDATE users SET is_active = 0 WHERE id = 1;
   • You should see: Query OK for both; the update says 1 row affected.
   • Check: SELECT name FROM users WHERE is_active = 1; → neha.
   • Why: The account disappears from the app, but the order history stays for bills and audits. If the user comes back, set is_active = 1.
10. Clean up
    • Command: DROP DATABASE shop;
    • You should see: Query OK, 2 rows affected (two tables removed).
    • Check: SHOW DATABASES; no longer lists shop. Exit and stop the instance.
    • Why: Dropping the whole practice database removes both tables in one step. Never run this on real data.

17. The whole class on one page

1. Keys go in CREATE TABLE: inline email VARCHAR(50) UNIQUE, or table level ..., UNIQUE(email) after a comma.
2. A duplicate gives ERROR 1062 ... for key 'email', and the failed insert uses up an id.
3. Database design: how many tables, which columns where, and how they link.
4. Counters can't say who or when. One row per event (likes), and SELECT COUNT(*) FROM likes WHERE reel_id = 102;
5. One value per row: a column. Many values per row: its own table.
6. Find tables by walking the user journey. Actions (search, filter, add to cart) are not columns.
7. Repeated text such as a category name becomes its own table plus an id column (category_id).
8. Foreign key: FOREIGN KEY (user_id) REFERENCES users(id). Parent = users, child = orders. DESC shows MUL.
9. The parent column must be a PRIMARY KEY or UNIQUE column of the same type; otherwise ERROR 1005 (errno 150).
10. A child without a parent: ERROR 1452. Deleting or changing a parent with children: ERROR 1451. Delete the child first.
11. Soft delete: is_active 1 / 0, and WHERE is_active = 1 in the app.

18. Try at home

Recap

Revision, comparison and interview cards

Revise this lesson in a few minutes. Save the cards on your phone or download them as PNG files. The QR code on each card opens this lesson.

More cards: Ravi Sir's Cards.