SQL LEFT JOIN

SQL Tutorial › Joins & Set Operators › LEFT JOIN

SQL LEFT JOIN

IntermediateLesson 33 of 6912 min read8 queries run on MySQL 8.4

LEFT JOIN (also LEFT OUTER JOIN) keeps every row of the left table, whether or not it has a match on the right. Where there is no match, the right table’s columns are NULL. That makes it the join for questions like “all customers, with their orders if they have any” and “which products have never been sold”.

In this lesson
  • Keep unmatched rows with LEFT JOIN
  • Find rows with no match using IS NULL
  • Count with zeros instead of losing rows
  • Avoid the WHERE clause that turns a LEFT JOIN into an INNER JOIN

LEFT JOIN syntax

MySQL
SELECT columnsFROM left_table lLEFT JOIN right_table r ON r.key = l.key;

Sample data

customers · 7 rows (some columns)
customer_idcustomer_namecity
1Ananya SharmaMumbai
2Rohit KumarDelhi
3Fatima KhanHyderabad
4David FernandesGoa
5Ishita BoseKolkata
6Manoj PillaiChennai
7Sara ThomasBengaluru
orders · 10 rows
order_idcustomer_idorder_datestatus
100112025-01-05Delivered
100222025-01-17Delivered
100312025-02-02Delivered
100432025-02-14Cancelled
100542025-03-08Delivered
100652025-03-22Shipped
100722025-04-10Delivered
100812025-04-28Pending
100932025-05-15Delivered
101052025-06-01Delivered

All customers, with their orders

SQL
SELECT c.customer_id, c.customer_name, o.order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idORDER BY c.customer_id, o.order_id;
ResultMySQL 8.4
customer_idcustomer_nameorder_idstatus
1Ananya Sharma1001Delivered
1Ananya Sharma1003Delivered
1Ananya Sharma1008Pending
2Rohit Kumar1002Delivered
2Rohit Kumar1007Delivered
3Fatima Khan1004Cancelled
3Fatima Khan1009Delivered
4David Fernandes1005Delivered
5Ishita Bose1006Shipped
5Ishita Bose1010Delivered
6Manoj PillaiNULLNULL
7Sara ThomasNULLNULL
12 rows in set
How this query works, step by step
  1. customers is the left table, so all 7 customers will appear.
  2. Customers with orders get one row per order, as with an inner join.
  3. Manoj (6) and Sara (7) have no orders: they still appear once, with NULL in order_id and status.
  4. Result: 10 matched rows + 2 unmatched = 12 rows.

Find rows with no match (anti-join)

Keep only the rows where the right side is NULL. Customers who never ordered:

SQL
SELECT c.customer_id, c.customer_nameFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.order_id IS NULL;
ResultMySQL 8.4
customer_idcustomer_name
6Manoj Pillai
7Sara Thomas
2 rows in set
How this query works, step by step
  1. The left join adds NULL order columns to customers without orders.
  2. WHERE o.order_id IS NULL keeps exactly those rows.
  3. Test a column that can never be NULL in a real match, such as the primary key order_id.

Products that have never been ordered:

SQL
SELECT p.product_id, p.product_nameFROM products pLEFT JOIN order_items oi ON oi.product_id = p.product_idWHERE oi.product_id IS NULL;
ResultMySQL 8.4
product_idproduct_name
9Monitor Arm
1 row in set

Departments with no employees:

SQL
SELECT d.dept_id, d.dept_nameFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idWHERE e.emp_id IS NULL;
ResultMySQL 8.4
dept_iddept_name
5Finance
1 row in set

Counts that include zero

Orders per customer, including customers with none:

SQL
SELECT c.customer_name, COUNT(o.order_id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idGROUP BY c.customer_id, c.customer_nameORDER BY orders DESC, c.customer_name;
ResultMySQL 8.4
customer_nameorders
Ananya Sharma3
Fatima Khan2
Ishita Bose2
Rohit Kumar2
David Fernandes1
Manoj Pillai0
Sara Thomas0
7 rows in set
How this query works, step by step
  1. The left join keeps all 7 customers.
  2. COUNT(o.order_id) counts non-NULL order ids, so Manoj and Sara get 0.
  3. COUNT(*) would be wrong here: it counts their single NULL row as 1.

The WHERE trap

Goal: all customers, plus their delivered orders. Putting the status filter in WHERE silently removes the customers without orders:

SQL
SELECT c.customer_name, o.order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.status = 'Delivered'ORDER BY c.customer_name;
ResultMySQL 8.4
customer_nameorder_idstatus
Ananya Sharma1001Delivered
Ananya Sharma1003Delivered
David Fernandes1005Delivered
Fatima Khan1009Delivered
Ishita Bose1010Delivered
Rohit Kumar1002Delivered
Rohit Kumar1007Delivered
7 rows in set
How this query works, step by step
  1. The left join produces NULL status for Manoj and Sara.
  2. WHERE o.status = 'Delivered' is UNKNOWN for NULL, so those rows are thrown away.
  3. The query now behaves like an inner join: the customers without delivered orders are gone.

Move the condition into ON, so it only decides which orders match:

MySQL
SELECT c.customer_name, o.order_id, o.statusFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'Delivered'ORDER BY c.customer_name;
ResultMySQL 8.4
customer_nameorder_idstatus
Ananya Sharma1003Delivered
Ananya Sharma1001Delivered
David Fernandes1005Delivered
Fatima Khan1009Delivered
Ishita Bose1010Delivered
Manoj PillaiNULLNULL
Rohit Kumar1007Delivered
Rohit Kumar1002Delivered
Sara ThomasNULLNULL
9 rows in set
How this query works, step by step
  1. Only delivered orders can match now.
  2. Manoj and Sara have no delivered order, so they still appear once, with NULLs.
  3. Every customer is in the result.
Tip

Rule of thumb for outer joins: conditions on the right table go in ON; conditions on the left table go in WHERE.

LEFT JOIN with several tables

Every product with its total units sold, including unsold products:

SQL
SELECT p.product_name, COALESCE(SUM(oi.quantity), 0) AS units_soldFROM products pLEFT JOIN order_items oi ON oi.product_id = p.product_idGROUP BY p.product_id, p.product_nameORDER BY units_sold DESC, p.product_name;
ResultMySQL 8.4
product_nameunits_sold
Notebook Pack18
Gel Pens6
Desk Lamp3
Mechanical Keyboard3
Office Chair3
USB-C Hub2
Wireless Mouse2
Standing Desk1
Monitor Arm0
9 rows in set

COALESCE turns the NULL sum of the Monitor Arm into 0.

Exercise: Employees and departments

List all employees with their department name, including the CEO who has no department.

Expected resultMySQL 8.4
first_namedept_name
MeeraNULL
AshaEngineering
RahulEngineering
PriyaEngineering
KaranEngineering
VikramSales
NehaSales
ArjunSales
KavyaMarketing
RohanMarketing
SnehaHR
AmitEngineering
12 rows in set
Hint 1

employees must be the left table.

Exercise: Headcount including empty departments

For each department, show its name and the number of employees, including departments with no employees.

Expected resultMySQL 8.4
dept_namestaff
Engineering5
Sales3
Marketing2
HR1
Finance0
5 rows in set
Hint 1

Departments on the left, COUNT(e.emp_id).

Quick quiz

1. In A LEFT JOIN B, which rows are always kept?

2. How do you find rows in A with no match in B?

3. Why use COUNT(o.order_id) instead of COUNT(*) after a LEFT JOIN?

4. A WHERE condition on the right table of a LEFT JOIN…

LEFT JOIN FAQ

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

None. OUTER is optional; both keep every row of the left table.

How do I find records that do not exist in another table?

Use LEFT JOIN other ON ... WHERE other.id IS NULL, or WHERE NOT EXISTS (SELECT 1 FROM other WHERE ...).

Why does my LEFT JOIN return the same result as an INNER JOIN?

Usually a WHERE condition on the right table removes the NULL rows. Move that condition into the ON clause.

Can a LEFT JOIN return more rows than the left table has?

Yes. If a left row matches several right rows, it appears once per match.

SQL INNER JOIN

SQL Tutorial › Joins & Set Operators › INNER JOIN

SQL INNER JOIN

IntermediateLesson 32 of 6912 min read6 queries run on MySQL 8.4

INNER JOIN returns the rows that have a match in both tables. It is by far the most used join: orders with their customers, order lines with their products, employees with their departments. This lesson builds inner joins step by step on the sample shop data, from two tables to four.

In this lesson
  • Write an INNER JOIN with ON
  • Use table aliases and qualified column names
  • Join three and four tables
  • Combine joins with WHERE, GROUP BY and USING

INNER JOIN syntax

MySQL
SELECT columnsFROM table1 t1INNER JOIN table2 t2 ON t2.key = t1.key;

INNER is optional: JOIN alone means INNER JOIN.

Sample data

orders · 10 rows
order_idcustomer_idorder_datestatus
100112025-01-05Delivered
100222025-01-17Delivered
100312025-02-02Delivered
100432025-02-14Cancelled
100542025-03-08Delivered
100652025-03-22Shipped
100722025-04-10Delivered
100812025-04-28Pending
100932025-05-15Delivered
101052025-06-01Delivered
customers · 7 rows (some columns)
customer_idcustomer_namecity
1Ananya SharmaMumbai
2Rohit KumarDelhi
3Fatima KhanHyderabad
4David FernandesGoa
5Ishita BoseKolkata
6Manoj PillaiChennai
7Sara ThomasBengaluru

Join two tables

Each order with its customer’s name:

SQL
SELECT o.order_id, o.order_date, c.customer_name, c.cityFROM orders oINNER JOIN customers c ON c.customer_id = o.customer_idORDER BY o.order_id;
ResultMySQL 8.4
order_idorder_datecustomer_namecity
10012025-01-05Ananya SharmaMumbai
10022025-01-17Rohit KumarDelhi
10032025-02-02Ananya SharmaMumbai
10042025-02-14Fatima KhanHyderabad
10052025-03-08David FernandesGoa
10062025-03-22Ishita BoseKolkata
10072025-04-10Rohit KumarDelhi
10082025-04-28Ananya SharmaMumbai
10092025-05-15Fatima KhanHyderabad
10102025-06-01Ishita BoseKolkata
10 rows in set
How this query works, step by step
  1. FROM orders o starts with the 10 orders; o is the table alias.
  2. For each order, INNER JOIN customers c ON c.customer_id = o.customer_id looks up the customer with the same id.
  3. Every order has a valid customer (the foreign key guarantees it), so all 10 orders come back.
  4. Customers 6 and 7 have no orders, so they never appear: an inner join keeps only matched rows.

How the matching works

Think of it as a nested loop: for every row on the left, find every row on the right where the ON condition is true, and output one combined row per match.

orders rowON c.customer_id = o.customer_idoutput
1001, customer 1matches Ananya Sharma1 row
1004, customer 3matches Fatima Khan1 row
(no order)Manoj Pillai, Sara Thomas never match0 rows

The database actually uses indexes and smarter algorithms, but the result is the same.

One-to-many joins multiply rows

An order can have several lines. Joining orders to order_items gives one row per line, not per order:

SQL
SELECT o.order_id, o.status, oi.product_id, oi.quantityFROM orders oJOIN order_items oi ON oi.order_id = o.order_idWHERE o.order_id IN (1001, 1002, 1005)ORDER BY o.order_id, oi.product_id;
ResultMySQL 8.4
order_idstatusproduct_idquantity
1001Delivered21
1001Delivered65
1002Delivered41
1005Delivered610
1005Delivered74
5 rows in set
How this query works, step by step
  1. Order 1001 has two lines, so it appears twice.
  2. Order 1002 has one line and appears once.
  3. This is expected, but remember it before you SUM order-level columns after such a join: they would be counted once per line.

Join three and four tables

Customer name, product name and line amount for every order line:

SQL
SELECT c.customer_name, o.order_id, p.product_name, oi.quantity,       oi.quantity * p.price AS amountFROM orders oJOIN customers   c  ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id   = o.order_idJOIN products    p  ON p.product_id  = oi.product_idWHERE c.customer_id = 1ORDER BY o.order_id, p.product_name;
ResultMySQL 8.4
customer_nameorder_idproduct_namequantityamount
Ananya Sharma1001Mechanical Keyboard13499
Ananya Sharma1001Notebook Pack51245
Ananya Sharma1003Mechanical Keyboard13499
Ananya Sharma1003Wireless Mouse1799
Ananya Sharma1008Desk Lamp22998
Ananya Sharma1008Office Chair217998
6 rows in set
How this query works, step by step
  1. orders joined to customers gives the customer’s name.
  2. Joining order_items expands each order into its lines.
  3. Joining products adds the name and price of each line.
  4. WHERE c.customer_id = 1 keeps Ananya’s lines: 5 lines across 3 orders.

INNER JOIN with GROUP BY

Revenue per customer, highest first:

SQL
SELECT c.customer_name,       COUNT(DISTINCT o.order_id) AS orders,       SUM(oi.quantity * p.price) AS revenueFROM customers cJOIN orders o       ON o.customer_id = c.customer_idJOIN order_items oi ON oi.order_id   = o.order_idJOIN products p     ON p.product_id  = oi.product_idWHERE o.status <> 'Cancelled'GROUP BY c.customer_id, c.customer_nameORDER BY revenue DESC;
ResultMySQL 8.4
customer_nameordersrevenue
Ananya Sharma330038
Rohit Kumar29798
Ishita Bose26642
Fatima Khan13499
David Fernandes13286
5 rows in set
How this query works, step by step
  1. The joins build one row per order line with its customer and price.
  2. WHERE drops the cancelled order.
  3. GROUP BY the customer adds up the line amounts and counts distinct orders.
  4. Customers without orders are not in the result: use a LEFT JOIN to include them with zero.

Conditions in ON vs WHERE

For an inner join, a filter gives the same result in ON or in WHERE:

SQL
SELECT o.order_id, c.customer_nameFROM orders oJOIN customers c ON c.customer_id = o.customer_id AND c.city = 'Delhi';
ResultMySQL 8.4
order_idcustomer_name
1002Rohit Kumar
1007Rohit Kumar
2 rows in set

Keep join conditions in ON and filters in WHERE for readability. For outer joins the placement changes the result; see LEFT JOIN.

USING

When both columns have the same name, USING (column) is a shortcut for ON a.column = b.column, and the shared column appears only once in SELECT *:

MySQL
SELECT order_id, customer_id, customer_nameFROM ordersJOIN customers USING (customer_id)WHERE order_id < 1004;
ResultMySQL 8.4
order_idcustomer_idcustomer_name
10011Ananya Sharma
10031Ananya Sharma
10022Rohit Kumar
3 rows in set

Common mistakes

  • Forgetting the ON condition (or writing FROM a, b with no WHERE) produces a cross join: every row times every row.
  • Joining on the wrong column, for example ON c.customer_id = o.order_id, runs without an error and returns nonsense. Always check the row count.
  • Ambiguous column names: if both tables have dept_id, write e.dept_id or d.dept_id.

Exercise: Products in order 1008

List the product names and quantities in order 1008.

Expected resultMySQL 8.4
product_namequantity
Office Chair2
Desk Lamp2
2 rows in set
Hint 1

Join order_items to products and filter on order_id.

Exercise: Employees and their department location

Show each employee’s first_name, dept_name and location, for employees whose department is in Mumbai.

Expected resultMySQL 8.4
first_namedept_namelocation
VikramSalesMumbai
NehaSalesMumbai
ArjunSalesMumbai
3 rows in set
Hint 1

Join employees to departments and filter on d.location.

Quick quiz

1. A customer has no orders. Does customers INNER JOIN orders return them?

2. Order 1001 has two order lines. How many rows does it produce in orders JOIN order_items?

3. What is JOIN without a keyword in MySQL?

INNER JOIN FAQ

What does INNER JOIN do in SQL?

It returns rows that have matching values in both tables, according to the condition in ON.

How do I join three tables in SQL?

Add one JOIN ... ON ... per extra table: FROM a JOIN b ON b.x = a.x JOIN c ON c.y = b.y.

Why does my join return duplicate rows?

In a one-to-many relationship each parent row appears once for every matching child row. Aggregate, use DISTINCT deliberately, or join to a pre-aggregated subquery.

Is INNER JOIN faster than LEFT JOIN?

Often slightly, because the optimizer has more freedom to reorder tables. Choose the join that gives the correct result first; indexes on the join columns matter far more.

SQL AUTO_INCREMENT

SQL Tutorial › Database Design › AUTO_INCREMENT

SQL AUTO_INCREMENT

BeginnerLesson 56 of 698 min read4 queries run on MySQL 8.4

AUTO_INCREMENT makes MySQL generate the next number for a column every time you insert a row, so you never have to work out a new id yourself. It is the standard way to create surrogate primary keys. This lesson shows how it counts, how to get the id you just created, and why the numbers sometimes skip.

In this lesson
  • Create an AUTO_INCREMENT primary key
  • Get the new id with LAST_INSERT_ID()
  • Set or reset the starting value
  • Understand gaps in the sequence

Define an AUTO_INCREMENT column

MySQL
CREATE TABLE tickets (  ticket_id INT AUTO_INCREMENT PRIMARY KEY,  title     VARCHAR(60) NOT NULL);INSERT INTO tickets (title) VALUES ('Login fails on Safari');INSERT INTO tickets (title) VALUES ('Report export is slow'), ('Typo on pricing page');SELECT * FROM tickets;
ResultMySQL 8.4
mysql> CREATE TABLE tickets (
Query OK, 0 rows affected
mysql> INSERT INTO tickets (title) VALUES ('Login fails on Safari')
Query OK, 1 row affected
mysql> INSERT INTO tickets (title) VALUES ('Report export is slow'), ('Typo o…
Query OK, 2 rows affected
mysql> SELECT * FROM tickets
ticket_idtitle
1Login fails on Safari
2Report export is slow
3Typo on pricing page
3 rows in set
How this query works, step by step
  1. ticket_id is not given in the inserts; MySQL fills in 1, 2 and 3.
  2. The column must be a key (usually the primary key), and a table can have only one AUTO_INCREMENT column.
  3. Inserting NULL or 0 into the column also generates the next value.

LAST_INSERT_ID()

After an insert, LAST_INSERT_ID() returns the id that was generated, which you need to insert child rows:

MySQL
CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(60) NOT NULL);CREATE TABLE ticket_comments (comment_id INT AUTO_INCREMENT PRIMARY KEY, ticket_id INT NOT NULL, body VARCHAR(100));INSERT INTO tickets (title) VALUES ('Checkout button missing');INSERT INTO ticket_comments (ticket_id, body) VALUES (LAST_INSERT_ID(), 'Reproduced on Chrome');SELECT t.ticket_id, t.title, c.body FROM tickets t JOIN ticket_comments c ON c.ticket_id = t.ticket_id;
ResultMySQL 8.4
mysql> CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title …
Query OK, 0 rows affected
mysql> CREATE TABLE ticket_comments (comment_id INT AUTO_INCREMENT PRIMARY KE…
Query OK, 0 rows affected
mysql> INSERT INTO tickets (title) VALUES ('Checkout button missing')
Query OK, 1 row affected
mysql> INSERT INTO ticket_comments (ticket_id, body) VALUES (LAST_INSERT_ID()…
Query OK, 1 row affected
mysql> SELECT t.ticket_id, t.title, c.body FROM tickets t JOIN ticket_comment…
ticket_idtitlebody
1Checkout button missingReproduced on Chrome
1 row in set
How this query works, step by step
  1. The ticket insert generates ticket_id = 1.
  2. LAST_INSERT_ID() returns 1, so the comment is attached to the right ticket.
  3. The value is per connection: other users inserting at the same time cannot change what your session sees.
  4. For a multi-row insert it returns the id of the first inserted row.

Choose the starting value

MySQL
CREATE TABLE invoices (  invoice_no INT AUTO_INCREMENT PRIMARY KEY,  amount     INT NOT NULL) AUTO_INCREMENT = 1001;INSERT INTO invoices (amount) VALUES (4999), (1250);SELECT * FROM invoices;
ResultMySQL 8.4
mysql> CREATE TABLE invoices (
Query OK, 0 rows affected
mysql> INSERT INTO invoices (amount) VALUES (4999), (1250)
Query OK, 2 rows affected
mysql> SELECT * FROM invoices
invoice_noamount
10014999
10021250
2 rows in set

Change it later with ALTER TABLE invoices AUTO_INCREMENT = 5000; (it cannot be set below the current maximum).

Why there are gaps

Auto-increment values are never reused. Deleted rows, rolled-back inserts and failed inserts all leave gaps:

MySQL
CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(60) NOT NULL UNIQUE);INSERT INTO tickets (title) VALUES ('A'), ('B'), ('C');DELETE FROM tickets WHERE ticket_id = 3;INSERT INTO tickets (title) VALUES ('D');INSERT IGNORE INTO tickets (title) VALUES ('A');INSERT INTO tickets (title) VALUES ('E');SELECT * FROM tickets;
ResultMySQL 8.4
mysql> CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title …
Query OK, 0 rows affected
mysql> INSERT INTO tickets (title) VALUES ('A'), ('B'), ('C')
Query OK, 3 rows affected
mysql> DELETE FROM tickets WHERE ticket_id = 3
Query OK, 1 row affected
mysql> INSERT INTO tickets (title) VALUES ('D')
Query OK, 1 row affected
mysql> INSERT IGNORE INTO tickets (title) VALUES ('A')
Query OK, 0 rows affected, 1 warning
mysql> INSERT INTO tickets (title) VALUES ('E')
Query OK, 1 row affected
mysql> SELECT * FROM tickets
ticket_idtitle
1A
2B
4D
6E
4 rows in set
How this query works, step by step
  1. A, B, C get 1, 2, 3. Deleting 3 does not free the number: D gets 4.
  2. INSERT IGNORE of the duplicate 'A' fails the unique check but has already used up number 5.
  3. E therefore gets 6.
Tip

Treat auto-increment ids as unique labels, not as a count or an order of events. If you need gap-free invoice numbers for legal reasons, generate them separately inside a transaction.

TRUNCATE resets the counter

DELETE FROM t keeps the counter; TRUNCATE TABLE t resets it to 1 (see DELETE & TRUNCATE).

In other databases

DatabaseSyntax
MySQLid INT AUTO_INCREMENT PRIMARY KEY
PostgreSQLid INT GENERATED ALWAYS AS IDENTITY (or SERIAL)
SQL Serverid INT IDENTITY(1,1) PRIMARY KEY
SQLiteid INTEGER PRIMARY KEY (auto-assigned)
Oracle 12c+id NUMBER GENERATED ALWAYS AS IDENTITY

Quick quiz

1. How do you get the id generated by your last insert in MySQL?

2. A row with id 7 is deleted. Is 7 reused?

3. Why is SELECT MAX(id) a bad way to find your new id?

AUTO_INCREMENT FAQ

What is AUTO_INCREMENT in MySQL?

A column attribute that makes MySQL assign the next integer automatically when a row is inserted without a value for that column.

How do I reset AUTO_INCREMENT in MySQL?

TRUNCATE TABLE t empties the table and resets it, or ALTER TABLE t AUTO_INCREMENT = n sets the next value (at least the current maximum + 1).

Why does my AUTO_INCREMENT skip numbers?

Deletes, rolled-back transactions, failed inserts and INSERT IGNORE / ON DUPLICATE KEY all consume values without leaving rows.

SQL CHECK Constraint

SQL Tutorial › Database Design › CHECK

SQL CHECK Constraint

IntermediateLesson 55 of 698 min read7 queries run on MySQL 8.4

A CHECK constraint validates each row against a condition: prices must be positive, ratings between 1 and 5, an end date after its start date. MySQL accepted the syntax for years but ignored it; since MySQL 8.0.16 checks are enforced, so they are now a real tool for keeping data valid.

In this lesson
  • Validate values with CHECK conditions
  • Write rules across several columns
  • Remember how CHECK treats NULL
  • Name, add, drop and disable checks

A column check

SQL
CREATE TABLE ratings (  rating_id INT PRIMARY KEY,  stars     INT NOT NULL CHECK (stars BETWEEN 1 AND 5),  comment   VARCHAR(200));INSERT INTO ratings VALUES (1, 5, 'Great course'), (2, 3, NULL);SELECT * FROM ratings;
ResultMySQL 8.4
mysql> CREATE TABLE ratings (
Query OK, 0 rows affected
mysql> INSERT INTO ratings VALUES (1, 5, 'Great course'), (2, 3, NULL)
Query OK, 2 rows affected
mysql> SELECT * FROM ratings
rating_idstarscomment
15Great course
23NULL
2 rows in set
MySQL
CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT NOT NULL CHECK (stars BETWEEN 1 AND 5));INSERT INTO ratings VALUES (3, 7);
ResultMySQL 8.4
mysql> CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT NOT NULL CH…
Query OK, 0 rows affected
mysql> INSERT INTO ratings VALUES (3, 7)
ERROR 3819 (HY000): Check constraint 'ratings_chk_1' is violated.
How this query works, step by step
  1. Every insert or update is tested with stars BETWEEN 1 AND 5.
  2. 7 fails, so MySQL rejects the row with error 3819 and names the check (ratings_chk_1).

A rule across columns

A table-level check can compare columns, and a name makes the error clearer:

MySQL
CREATE TABLE promotions (  promo_id   INT PRIMARY KEY,  discount   INT NOT NULL,  start_date DATE NOT NULL,  end_date   DATE NOT NULL,  CONSTRAINT chk_discount CHECK (discount > 0 AND discount <= 70),  CONSTRAINT chk_dates CHECK (end_date >= start_date));INSERT INTO promotions VALUES (1, 20, '2026-01-01', '2026-01-31');INSERT INTO promotions VALUES (2, 10, '2026-03-10', '2026-03-01');
ResultMySQL 8.4
mysql> CREATE TABLE promotions (
Query OK, 0 rows affected
mysql> INSERT INTO promotions VALUES (1, 20, '2026-01-01', '2026-01-31')
Query OK, 1 row affected
mysql> INSERT INTO promotions VALUES (2, 10, '2026-03-10', '2026-03-01')
ERROR 3819 (HY000): Check constraint 'chk_dates' is violated.
How this query works, step by step
  1. Promotion 1 passes both checks.
  2. Promotion 2 ends before it starts, so chk_dates rejects it, and the error message names it.

A list of allowed values:

SQL
CREATE TABLE shipments (  shipment_id INT PRIMARY KEY,  status VARCHAR(12) NOT NULL CHECK (status IN ('packed', 'shipped', 'delivered')));INSERT INTO shipments VALUES (1, 'packed'), (2, 'shipped');SELECT * FROM shipments;
ResultMySQL 8.4
mysql> CREATE TABLE shipments (
Query OK, 0 rows affected
mysql> INSERT INTO shipments VALUES (1, 'packed'), (2, 'shipped')
Query OK, 2 rows affected
mysql> SELECT * FROM shipments
shipment_idstatus
1packed
2shipped
2 rows in set

CHECK and NULL

A check passes when its condition is UNKNOWN, which happens when a value is NULL:

SQL
CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT CHECK (stars BETWEEN 1 AND 5));INSERT INTO ratings VALUES (1, NULL);SELECT * FROM ratings;
ResultMySQL 8.4
mysql> CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT CHECK (star…
Query OK, 0 rows affected
mysql> INSERT INTO ratings VALUES (1, NULL)
Query OK, 1 row affected
mysql> SELECT * FROM ratings
rating_idstars
1NULL
1 row in set

The row is accepted because NULL BETWEEN 1 AND 5 is UNKNOWN, not FALSE. Add NOT NULL when the value is required.

Add, drop and disable a check

MySQL
ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);SELECT constraint_name, check_clause FROM information_schema.check_constraintsWHERE constraint_schema = 'company';
ResultMySQL 8.4
mysql> ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0)
Query OK, 9 rows affected
mysql> SELECT constraint_name, check_clause FROM information_schema.check_con…
CONSTRAINT_NAMECHECK_CLAUSE
chk_stock(`stock` >= 0)
1 row in set
MySQL
ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);UPDATE products SET stock = -5 WHERE product_id = 1;
ResultMySQL 8.4
mysql> ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0)
Query OK, 9 rows affected
mysql> UPDATE products SET stock = -5 WHERE product_id = 1
ERROR 3819 (HY000): Check constraint 'chk_stock' is violated.
  • ALTER TABLE products DROP CHECK chk_stock; removes it.
  • ALTER TABLE products ALTER CHECK chk_stock NOT ENFORCED; keeps it but stops checking (useful for bulk loads).
Note

A check may use only the row’s own columns and deterministic functions. It cannot use subqueries, other tables, NOW() or user variables. For rules that involve other tables, use a foreign key or a trigger.

Quick quiz

1. From which MySQL version are CHECK constraints enforced?

2. A column has CHECK (age >= 18). Is a NULL age accepted?

3. Can a CHECK constraint compare two columns of the same row?

CHECK FAQ

Does MySQL support CHECK constraints?

Yes. MySQL 8.0.16 and later enforce CHECK constraints; older versions ignored them.

Can a CHECK constraint reference another table?

No. Checks see only the current row. Use a foreign key or a trigger for cross-table rules.

What is the difference between CHECK and ENUM?

ENUM is a data type that restricts a column to a list. A CHECK can express any row-level rule, including ranges and comparisons between columns, and keeps the column a normal type.

SQL NOT NULL Constraint

SQL Tutorial › Database Design › NOT NULL

SQL NOT NULL Constraint

BeginnerLesson 51 of 697 min read6 queries run on MySQL 8.4

By default any column may hold NULL. A NOT NULL constraint says “this column must always have a value”. It is the simplest and most useful constraint: names, dates and amounts that are required by the business should never be missing, and the database can guarantee that.

In this lesson
  • Declare required columns with NOT NULL
  • Recognise NOT NULL errors on INSERT and UPDATE
  • Add NOT NULL to an existing column safely
  • Combine NOT NULL with DEFAULT

Define NOT NULL columns

MySQL
CREATE TABLE patients (  patient_id INT PRIMARY KEY,  full_name  VARCHAR(60) NOT NULL,  phone      VARCHAR(15),  admitted   DATE NOT NULL);DESCRIBE patients;
ResultMySQL 8.4
mysql> CREATE TABLE patients (
Query OK, 0 rows affected
mysql> DESCRIBE patients
FieldTypeNullKeyDefaultExtra
patient_idintNOPRINULL
full_namevarchar(60)NONULL
phonevarchar(15)YESNULL
admitteddateNONULL
4 rows in set
How this query works, step by step
  1. full_name and admitted are NOT NULL; the Null column of DESCRIBE shows NO.
  2. phone is optional (YES).
  3. A primary key is automatically NOT NULL.

Inserting without a required value

Leaving a NOT NULL column out:

MySQL
CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(60) NOT NULL, phone VARCHAR(15), admitted DATE NOT NULL);INSERT INTO patients (patient_id, full_name) VALUES (1, 'Ravi Shah');
ResultMySQL 8.4
mysql> CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(6…
Query OK, 0 rows affected
mysql> INSERT INTO patients (patient_id, full_name) VALUES (1, 'Ravi Shah')
ERROR 1364 (HY000): Field 'admitted' doesn't have a default value

Giving NULL explicitly:

MySQL
CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(60) NOT NULL, phone VARCHAR(15), admitted DATE NOT NULL);INSERT INTO patients VALUES (1, NULL, '98450 11111', '2026-01-10');
ResultMySQL 8.4
mysql> CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(6…
Query OK, 0 rows affected
mysql> INSERT INTO patients VALUES (1, NULL, '98450 11111', '2026-01-10')
ERROR 1048 (23000): Column 'full_name' cannot be null
How this query works, step by step
  1. Error 1364: the column was not given and has no default.
  2. Error 1048: an explicit NULL was given for a NOT NULL column.
  3. An UPDATE ... SET full_name = NULL fails with error 1048 in the same way.
Note

An empty string '' is not NULL, so NOT NULL does not stop it. Add a CHECK (full_name <> '') if empty text must be rejected too.

NOT NULL with DEFAULT

Combine them when a value is required but has an obvious standard:

SQL
CREATE TABLE tickets (  ticket_id INT PRIMARY KEY,  status    VARCHAR(10) NOT NULL DEFAULT 'open');INSERT INTO tickets (ticket_id) VALUES (1);SELECT * FROM tickets;
ResultMySQL 8.4
mysql> CREATE TABLE tickets (
Query OK, 0 rows affected
mysql> INSERT INTO tickets (ticket_id) VALUES (1)
Query OK, 1 row affected
mysql> SELECT * FROM tickets
ticket_idstatus
1open
1 row in set

Add NOT NULL to an existing column

MODIFY redefines the column. It fails if existing rows contain NULL:

MySQL
ALTER TABLE customers MODIFY email VARCHAR(60) NOT NULL;
ResultMySQL 8.4
ERROR 1138 (22004): Invalid use of NULL value

Fix the data first, then add the constraint:

MySQL
UPDATE customers SET email = 'unknown@example.com' WHERE email IS NULL;ALTER TABLE customers MODIFY email VARCHAR(60) NOT NULL;SELECT customer_id, email FROM customers WHERE customer_id = 4;
ResultMySQL 8.4
mysql> UPDATE customers SET email = 'unknown@example.com' WHERE email IS NULL
Query OK, 1 row affected
mysql> ALTER TABLE customers MODIFY email VARCHAR(60) NOT NULL
Query OK, 0 rows affected
mysql> SELECT customer_id, email FROM customers WHERE customer_id = 4
customer_idemail
4unknown@example.com
1 row in set
How this query works, step by step
  1. Customer 4 has no email; the UPDATE fills in a placeholder.
  2. Now no row is NULL, so MODIFY ... NOT NULL succeeds.
  3. MODIFY must repeat the full column type (VARCHAR(60)), or the type would change too.

Remove the constraint again by modifying the column without NOT NULL:

MySQL
ALTER TABLE customers MODIFY email VARCHAR(60) NULL;

Quick quiz

1. Does NOT NULL reject an empty string ”?

2. Why does ALTER TABLE ... MODIFY email ... NOT NULL fail?

NOT NULL FAQ

What does NOT NULL mean in SQL?

The column must always contain a value; inserts or updates that would leave it NULL are rejected.

How do I add a NOT NULL constraint to an existing column in MySQL?

Update existing NULLs to a value, then run ALTER TABLE t MODIFY col TYPE NOT NULL; repeating the column’s full type.

Is a primary key NOT NULL automatically?

Yes. Every primary key column is implicitly NOT NULL.

SQL UNIQUE Constraint

SQL Tutorial › Database Design › UNIQUE

SQL UNIQUE Constraint

BeginnerLesson 52 of 698 min read6 queries run on MySQL 8.4

A UNIQUE constraint guarantees that no two rows share the same value in a column, or the same combination of values in several columns. Use it for natural identifiers that are not the primary key: email addresses, usernames, product codes, “one review per user per product”.

In this lesson
  • Make a column unique
  • Make a combination of columns unique
  • Understand how UNIQUE treats NULL
  • Add and drop unique constraints, and compare with PRIMARY KEY

A unique column

SQL
CREATE TABLE users (  user_id  INT PRIMARY KEY,  username VARCHAR(30) NOT NULL UNIQUE,  email    VARCHAR(100) UNIQUE);INSERT INTO users VALUES (1, 'asha', 'asha@example.com'), (2, 'ravi', 'ravi@example.com');SELECT * FROM users;
ResultMySQL 8.4
mysql> CREATE TABLE users (
Query OK, 0 rows affected
mysql> INSERT INTO users VALUES (1, 'asha', 'asha@example.com'), (2, 'ravi', …
Query OK, 2 rows affected
mysql> SELECT * FROM users
user_idusernameemail
1ashaasha@example.com
2raviravi@example.com
2 rows in set

A duplicate is rejected:

MySQL
CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(30) NOT NULL UNIQUE, email VARCHAR(100) UNIQUE);INSERT INTO users VALUES (1, 'asha', 'asha@example.com');INSERT INTO users VALUES (2, 'asha', 'other@example.com');
ResultMySQL 8.4
mysql> CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(30) NOT …
Query OK, 0 rows affected
mysql> INSERT INTO users VALUES (1, 'asha', 'asha@example.com')
Query OK, 1 row affected
mysql> INSERT INTO users VALUES (2, 'asha', 'other@example.com')
ERROR 1062 (23000): Duplicate entry 'asha' for key 'users.username'
How this query works, step by step
  1. The first row is inserted.
  2. The second row has a different id but the same username, so MySQL rejects it with error 1062 and names the key: users.username.
  3. With the default case-insensitive collation, 'Asha' would also count as a duplicate of 'asha'.

UNIQUE and NULL

A unique column may hold many NULLs, because NULL is not equal to anything, not even another NULL:

SQL
CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(30) NOT NULL UNIQUE, email VARCHAR(100) UNIQUE);INSERT INTO users VALUES (1, 'asha', NULL), (2, 'ravi', NULL), (3, 'mona', NULL);SELECT COUNT(*) AS users_without_email FROM users WHERE email IS NULL;
ResultMySQL 8.4
mysql> CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(30) NOT …
Query OK, 0 rows affected
mysql> INSERT INTO users VALUES (1, 'asha', NULL), (2, 'ravi', NULL), (3, 'mo…
Query OK, 3 rows affected
mysql> SELECT COUNT(*) AS users_without_email FROM users WHERE email IS NULL
users_without_email
3
1 row in set

Add NOT NULL if every row must have a distinct, real value.

Composite unique key

One review per customer per product: the pair must be unique, each column alone may repeat:

SQL
CREATE TABLE reviews (  review_id   INT PRIMARY KEY,  customer_id INT NOT NULL,  product_id  INT NOT NULL,  rating      INT NOT NULL,  CONSTRAINT uq_review UNIQUE (customer_id, product_id));INSERT INTO reviews VALUES (1, 1, 2, 5), (2, 1, 4, 4), (3, 2, 2, 3);SELECT * FROM reviews;
ResultMySQL 8.4
mysql> CREATE TABLE reviews (
Query OK, 0 rows affected
mysql> INSERT INTO reviews VALUES (1, 1, 2, 5), (2, 1, 4, 4), (3, 2, 2, 3)
Query OK, 3 rows affected
mysql> SELECT * FROM reviews
review_idcustomer_idproduct_idrating
1125
2144
3223
3 rows in set
MySQL
CREATE TABLE reviews (review_id INT PRIMARY KEY, customer_id INT NOT NULL, product_id INT NOT NULL, rating INT NOT NULL, CONSTRAINT uq_review UNIQUE (customer_id, product_id));INSERT INTO reviews VALUES (1, 1, 2, 5);INSERT INTO reviews VALUES (4, 1, 2, 1);
ResultMySQL 8.4
mysql> CREATE TABLE reviews (review_id INT PRIMARY KEY, customer_id INT NOT N…
Query OK, 0 rows affected
mysql> INSERT INTO reviews VALUES (1, 1, 2, 5)
Query OK, 1 row affected
mysql> INSERT INTO reviews VALUES (4, 1, 2, 1)
ERROR 1062 (23000): Duplicate entry '1-2' for key 'reviews.uq_review'
How this query works, step by step
  1. Customer 1 may review products 2 and 4, and customer 2 may also review product 2.
  2. A second review by customer 1 of product 2 repeats the pair (1, 2) and is rejected.

Add and drop UNIQUE

MySQL
ALTER TABLE customers ADD CONSTRAINT uq_customer_email UNIQUE (email);SHOW INDEX FROM customers WHERE Key_name = 'uq_customer_email';ALTER TABLE customers DROP INDEX uq_customer_email;
ResultMySQL 8.4
mysql> ALTER TABLE customers ADD CONSTRAINT uq_customer_email UNIQUE (email)
Query OK, 0 rows affected
mysql> SHOW INDEX FROM customers WHERE Key_name = 'uq_customer_email'
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
customers0uq_customer_email1emailA7NULLNULLYESBTREEYESNULL
1 row in set
mysql> ALTER TABLE customers DROP INDEX uq_customer_email
Query OK, 0 rows affected
How this query works, step by step
  1. In MySQL a unique constraint is implemented as a unique index, which also speeds up lookups by email.
  2. SHOW INDEX lists it; Non_unique = 0 means unique.
  3. It is dropped with DROP INDEX (or DROP CONSTRAINT in MySQL 8.0.19+).

UNIQUE vs PRIMARY KEY

PRIMARY KEYUNIQUE
Per tableonemany
NULL allowednoyes (many)
Purposethe row’s identity, target of foreign keysbusiness rules on other columns
InnoDB storagerows are stored in primary-key orderseparate index

Quick quiz

1. How many NULLs can a UNIQUE column hold in MySQL?

2. UNIQUE (customer_id, product_id) rejects…

3. How many UNIQUE constraints can a table have?

UNIQUE FAQ

What is a UNIQUE constraint in SQL?

A rule that no two rows may have the same value (or combination of values) in the constrained columns.

Can a UNIQUE column be NULL?

Yes. In MySQL a unique column can contain multiple NULLs; add NOT NULL to prevent them.

Is a UNIQUE constraint an index?

In MySQL, yes: each unique constraint creates a unique index, which also speeds up searches on those columns.

SQL FOREIGN KEY

SQL Tutorial › Database Design › FOREIGN KEY

SQL FOREIGN KEY

IntermediateLesson 54 of 6912 min read6 queries run on MySQL 8.4

A foreign key links a column in one table (the child) to the primary key of another (the parent). The database then guarantees referential integrity: no order for a customer who does not exist, no employee in a department that was deleted. This lesson shows the errors a foreign key raises and the actions (CASCADE, SET NULL, RESTRICT) that decide what happens when a parent row changes.

In this lesson
  • Create a foreign key between parent and child tables
  • Understand errors 1452 (bad child) and 1451 (parent in use)
  • Choose ON DELETE / ON UPDATE actions
  • Add and drop foreign keys

Parent and child

In the sample database, orders.customer_id references customers.customer_id:

customers · 7 rows (some columns)
customer_idcustomer_name
1Ananya Sharma
2Rohit Kumar
3Fatima Khan
4David Fernandes
5Ishita Bose
6Manoj Pillai
7Sara Thomas
orders · 10 rows
order_idcustomer_idorder_datestatus
100112025-01-05Delivered
100222025-01-17Delivered
100312025-02-02Delivered
100432025-02-14Cancelled
100542025-03-08Delivered
100652025-03-22Shipped
100722025-04-10Delivered
100812025-04-28Pending
100932025-05-15Delivered
101052025-06-01Delivered
  • customers is the parent table; orders is the child.
  • Every orders.customer_id must exist in customers.

Create a foreign key

SQL
CREATE TABLE teams (  team_id   INT PRIMARY KEY,  team_name VARCHAR(30) NOT NULL);CREATE TABLE players (  player_id INT PRIMARY KEY,  name      VARCHAR(30) NOT NULL,  team_id   INT,  CONSTRAINT fk_player_team FOREIGN KEY (team_id) REFERENCES teams (team_id));INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers');INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2), (12, 'Mona', NULL);SELECT p.name, t.team_name FROM players p LEFT JOIN teams t ON t.team_id = p.team_id;
ResultMySQL 8.4
mysql> CREATE TABLE teams (
Query OK, 0 rows affected
mysql> CREATE TABLE players (
Query OK, 0 rows affected
mysql> INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers')
Query OK, 2 rows affected
mysql> INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2), (12, 'Mon…
Query OK, 3 rows affected
mysql> SELECT p.name, t.team_name FROM players p LEFT JOIN teams t ON t.team_…
nameteam_name
AshaTesters
RaviDevelopers
MonaNULL
3 rows in set
How this query works, step by step
  1. The parent table teams must exist first, with a primary (or unique) key.
  2. FOREIGN KEY (team_id) REFERENCES teams (team_id) ties players.team_id to it.
  3. Mona has NULL: a nullable foreign key means “not assigned”, which is allowed.
  4. MySQL automatically creates an index on players.team_id for the foreign key.

Error 1452: child row without a parent

MySQL
INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (2001, 99, '2026-01-10', 'Pending');
ResultMySQL 8.4
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

Error 1451: parent row still in use

MySQL
DELETE FROM customers WHERE customer_id = 1;
ResultMySQL 8.4
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))
How this query works, step by step
  1. Customer 99 does not exist, so the new order is rejected (1452).
  2. Customer 1 has orders, so deleting the customer would orphan them (1451).
  3. The default action is RESTRICT / NO ACTION: refuse the change.

ON DELETE CASCADE

With ON DELETE CASCADE, deleting a parent row deletes its child rows automatically:

MySQL
CREATE TABLE teams (team_id INT PRIMARY KEY, team_name VARCHAR(30) NOT NULL);CREATE TABLE players (  player_id INT PRIMARY KEY,  name      VARCHAR(30) NOT NULL,  team_id   INT,  FOREIGN KEY (team_id) REFERENCES teams (team_id) ON DELETE CASCADE);INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers');INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2), (12, 'Karan', 2);DELETE FROM teams WHERE team_id = 2;SELECT * FROM players;
ResultMySQL 8.4
mysql> CREATE TABLE teams (team_id INT PRIMARY KEY, team_name VARCHAR(30) NOT…
Query OK, 0 rows affected
mysql> CREATE TABLE players (
Query OK, 0 rows affected
mysql> INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers')
Query OK, 2 rows affected
mysql> INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2), (12, 'Kar…
Query OK, 3 rows affected
mysql> DELETE FROM teams WHERE team_id = 2
Query OK, 1 row affected
mysql> SELECT * FROM players
player_idnameteam_id
10Asha1
1 row in set
How this query works, step by step
  1. Team 2 has two players.
  2. DELETE FROM teams WHERE team_id = 2 removes the team and both of its players.
  3. MySQL reports only 1 row affected (the team); the cascaded rows are not counted.
Watch out

CASCADE is powerful and silent. One DELETE on a parent can remove thousands of child rows. Use it for true “part of” relationships (an order’s lines) and not for important independent data.

ON DELETE SET NULL

MySQL
CREATE TABLE teams (team_id INT PRIMARY KEY, team_name VARCHAR(30) NOT NULL);CREATE TABLE players (  player_id INT PRIMARY KEY,  name      VARCHAR(30) NOT NULL,  team_id   INT,  FOREIGN KEY (team_id) REFERENCES teams (team_id) ON DELETE SET NULL);INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers');INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2);DELETE FROM teams WHERE team_id = 2;SELECT * FROM players;
ResultMySQL 8.4
mysql> CREATE TABLE teams (team_id INT PRIMARY KEY, team_name VARCHAR(30) NOT…
Query OK, 0 rows affected
mysql> CREATE TABLE players (
Query OK, 0 rows affected
mysql> INSERT INTO teams VALUES (1, 'Testers'), (2, 'Developers')
Query OK, 2 rows affected
mysql> INSERT INTO players VALUES (10, 'Asha', 1), (11, 'Ravi', 2)
Query OK, 2 rows affected
mysql> DELETE FROM teams WHERE team_id = 2
Query OK, 1 row affected
mysql> SELECT * FROM players
player_idnameteam_id
10Asha1
11RaviNULL
2 rows in set

Ravi stays, with team_id set to NULL: the player exists, the team does not.

The actions

ActionWhen the parent row is deleted (or its key updated)
RESTRICT / NO ACTION (default)refuse the change while children exist
CASCADEdelete (or update) the child rows too
SET NULLset the child’s foreign key column to NULL
SET DEFAULTnot supported by InnoDB

ON UPDATE CASCADE works the same way for changes to the parent’s key value.

Add and drop a foreign key

MySQL
CREATE TABLE reviews (review_id INT PRIMARY KEY, product_id INT, rating INT);ALTER TABLE reviews  ADD CONSTRAINT fk_review_product FOREIGN KEY (product_id) REFERENCES products (product_id);SELECT constraint_name, referenced_table_nameFROM information_schema.referential_constraintsWHERE constraint_schema = 'company' AND table_name = 'reviews';ALTER TABLE reviews DROP FOREIGN KEY fk_review_product;
ResultMySQL 8.4
mysql> CREATE TABLE reviews (review_id INT PRIMARY KEY, product_id INT, ratin…
Query OK, 0 rows affected
mysql> ALTER TABLE reviews
Query OK, 0 rows affected
mysql> SELECT constraint_name, referenced_table_name
CONSTRAINT_NAMEREFERENCED_TABLE_NAME
fk_review_productproducts
1 row in set
mysql> ALTER TABLE reviews DROP FOREIGN KEY fk_review_product
Query OK, 0 rows affected

Rules for a valid foreign key

  • The parent column must be a primary key or have a unique index.
  • Both columns must have the same type (INT to INT, same sign and length for strings).
  • Both tables must use InnoDB (the default).
  • Existing child values must already exist in the parent when you add the key.

Quick quiz

1. Which table does the foreign key constraint live on?

2. What happens with ON DELETE CASCADE when a parent row is deleted?

3. Error 1452 means…

4. Can a foreign key column be NULL?

Foreign key FAQ

What is a foreign key in SQL?

A column whose values must match the primary or unique key of another table, so related rows always exist.

What is ON DELETE CASCADE?

An option on a foreign key: when a parent row is deleted, all child rows referencing it are deleted automatically.

How do I fix “Cannot add or update a child row: a foreign key constraint fails”?

Insert the parent row first, or correct the child value so it matches an existing parent key.

Do foreign keys slow down inserts?

Slightly, because each insert checks the parent index. The protection against orphaned data is almost always worth it.

SQL PRIMARY KEY

SQL Tutorial › Database Design › PRIMARY KEY

SQL PRIMARY KEY

BeginnerLesson 53 of 699 min read7 queries run on MySQL 8.4

The primary key is the column (or set of columns) that identifies each row uniquely. It cannot be NULL, cannot repeat, and is what other tables point to with foreign keys. Every table should have one, and choosing it well is one of the first decisions in database design.

In this lesson
  • Define single-column and composite primary keys
  • Understand the errors a primary key prevents
  • Choose between natural and surrogate keys
  • Add, drop and inspect a primary key

Define a primary key

SQL
CREATE TABLE books (  book_id INT PRIMARY KEY,  isbn    CHAR(13) NOT NULL UNIQUE,  title   VARCHAR(100) NOT NULL);INSERT INTO books VALUES (1, '9780134685991', 'Effective Java'), (2, '9781492056355', 'Learning SQL');SELECT * FROM books;
ResultMySQL 8.4
mysql> CREATE TABLE books (
Query OK, 0 rows affected
mysql> INSERT INTO books VALUES (1, '9780134685991', 'Effective Java'), (2, '…
Query OK, 2 rows affected
mysql> SELECT * FROM books
book_idisbntitle
19780134685991Effective Java
29781492056355Learning SQL
2 rows in set
How this query works, step by step
  1. book_id INT PRIMARY KEY makes book_id unique and NOT NULL.
  2. isbn is also unique, but it is a business value; the short integer book_id is the key other tables will reference.

What a primary key prevents

MySQL
CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL);INSERT INTO books VALUES (1, 'Effective Java');INSERT INTO books VALUES (1, 'Learning SQL');
ResultMySQL 8.4
mysql> CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100) NOT NU…
Query OK, 0 rows affected
mysql> INSERT INTO books VALUES (1, 'Effective Java')
Query OK, 1 row affected
mysql> INSERT INTO books VALUES (1, 'Learning SQL')
ERROR 1062 (23000): Duplicate entry '1' for key 'books.PRIMARY'
MySQL
CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL);INSERT INTO books VALUES (NULL, 'Learning SQL');
ResultMySQL 8.4
mysql> CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100) NOT NU…
Query OK, 0 rows affected
mysql> INSERT INTO books VALUES (NULL, 'Learning SQL')
ERROR 1048 (23000): Column 'book_id' cannot be null
How this query works, step by step
  1. Error 1062: a second row with book_id = 1 is a duplicate entry for key PRIMARY.
  2. Error 1048: the key cannot be NULL.

A table can have only one primary key:

MySQL
CREATE TABLE t (a INT PRIMARY KEY, b INT PRIMARY KEY);
ResultMySQL 8.4
ERROR 1068 (42000): Multiple primary key defined

Composite primary key

When no single column is unique, the key can be a combination. In order_items, an order has many products and a product appears in many orders, but each (order_id, product_id) pair appears once:

MySQL
SHOW KEYS FROM order_items WHERE Key_name = 'PRIMARY';
ResultMySQL 8.4
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
order_items0PRIMARY1order_idA1NULLNULLBTREEYESNULL
order_items0PRIMARY2product_idA1NULLNULLBTREEYESNULL
2 rows in set
MySQL
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1001, 2, 3);
ResultMySQL 8.4
ERROR 1062 (23000): Duplicate entry '1001-2' for key 'order_items.PRIMARY'
How this query works, step by step
  1. SHOW KEYS lists the two columns of the primary key in order (Seq_in_index 1 and 2).
  2. Order 1001 already contains product 2, so the same pair is rejected; to change the quantity, UPDATE the existing line.

Natural vs surrogate keys

Natural keySurrogate key
Exampleemail, ISBN, PAN numbercustomer_id INT AUTO_INCREMENT
Meaningreal-world valuenone, just a number
Can change?yes (emails change)never
Sizeoften long text4 or 8 bytes

Most designs use a surrogate primary key (often AUTO_INCREMENT) and protect the natural identifier with a UNIQUE constraint, as the books table above does with isbn.

Tip

In InnoDB, rows are physically stored in primary-key order and every secondary index stores the primary key. A short, ever-increasing key (such as an auto-increment integer) keeps tables and indexes compact and inserts fast.

Add or drop a primary key

MySQL
CREATE TABLE visits (visitor VARCHAR(30), visit_date DATE, pages INT);INSERT INTO visits VALUES ('asha', '2026-01-01', 3), ('asha', '2026-01-02', 5);ALTER TABLE visits ADD PRIMARY KEY (visitor, visit_date);SHOW KEYS FROM visits WHERE Key_name = 'PRIMARY';
ResultMySQL 8.4
mysql> CREATE TABLE visits (visitor VARCHAR(30), visit_date DATE, pages INT)
Query OK, 0 rows affected
mysql> INSERT INTO visits VALUES ('asha', '2026-01-01', 3), ('asha', '2026-01…
Query OK, 2 rows affected
mysql> ALTER TABLE visits ADD PRIMARY KEY (visitor, visit_date)
Query OK, 0 rows affected
mysql> SHOW KEYS FROM visits WHERE Key_name = 'PRIMARY'
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
visits0PRIMARY1visitorA1NULLNULLBTREEYESNULL
visits0PRIMARY2visit_dateA2NULLNULLBTREEYESNULL
2 rows in set
How this query works, step by step
  1. The table is created without a key.
  2. ALTER TABLE ... ADD PRIMARY KEY adds a composite key; it works because the existing rows are unique and not NULL.
  3. ALTER TABLE visits DROP PRIMARY KEY; would remove it again.

Quick quiz

1. How many primary keys can a table have?

2. Can a primary key column contain NULL?

3. What is a surrogate key?

Primary key FAQ

What is a primary key in SQL?

A column or group of columns whose values uniquely identify each row. It must be unique and cannot be NULL.

What is a composite primary key?

A primary key made of two or more columns, where only the combination must be unique, e.g. (order_id, product_id).

What is the difference between primary key and unique key?

A table has one primary key and it cannot contain NULL; it can have many unique keys, which may contain NULLs.

Should every table have a primary key?

Yes. Without one, rows cannot be identified reliably, and InnoDB creates a hidden key that you cannot use.

Python SQLite

Python Tutorial › Libraries & Databases › SQLite

Python SQLite

IntermediateLesson 47 of 4712 min read8 runnable examples

SQLite is a complete SQL database stored in a single file, and Python includes the sqlite3 module to use it, with nothing to install. It is ideal for small apps, test data and learning SQL from Python.

In this lesson
  • Connect and create tables
  • Insert rows safely with parameters
  • Query, update and delete
  • Use transactions and load results into pandas

Connect and create a table

sqlite3.connect() opens a database file, creating it if it does not exist. A cursor runs SQL statements.

Python
import sqlite3 conn = sqlite3.connect("shop.db")cur = conn.cursor()cur.execute("DROP TABLE IF EXISTS products")cur.execute("""    CREATE TABLE products (        id INTEGER PRIMARY KEY AUTOINCREMENT,        name TEXT NOT NULL,        category TEXT,        price REAL,        stock INTEGER DEFAULT 0    )""")conn.commit()print(cur.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall())conn.close()
Output
[('products',), ('sqlite_sequence',)]
Tip

sqlite3.connect(":memory:") creates a temporary database in memory, which is perfect for tests.

Insert rows with parameters

Use ? placeholders and pass the values separately. Never build SQL with f-strings from user input; that opens the door to SQL injection.

Python
import sqlite3 conn = sqlite3.connect("shop.db")cur = conn.cursor()cur.execute("INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)",            ("Keyboard", "electronics", 1499.0, 25)) items = [    ("Mouse", "electronics", 349.0, 60),    ("Notebook", "stationery", 45.0, 300),    ("Pen", "stationery", 10.0, 0),    ("Monitor", "electronics", 12999.0, 8),]cur.executemany("INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)", items)conn.commit()print("last id:", cur.lastrowid, "rows:", cur.execute("SELECT COUNT(*) FROM products").fetchone()[0])conn.close()
Output
last id: 1 rows: 5

Query rows

Python
import sqlite3 conn = sqlite3.connect("shop.db")cur = conn.cursor() for row in cur.execute("SELECT id, name, price FROM products ORDER BY price DESC LIMIT 3"):    print(row) cur.execute("SELECT name FROM products WHERE category = ? AND stock > ?", ("electronics", 10))print(cur.fetchall()) print(cur.execute("SELECT AVG(price) FROM products WHERE category = 'stationery'").fetchone())conn.close()
Output
(5, 'Monitor', 12999.0)
(1, 'Keyboard', 1499.0)
(2, 'Mouse', 349.0)
[('Keyboard',), ('Mouse',)]
(27.5,)
MethodReturns
fetchone()the next row as a tuple, or None
fetchmany(n)a list of up to n rows
fetchall()a list of all remaining rows

Rows as dictionaries

Set row_factory to sqlite3.Row to read columns by name:

Python
import sqlite3 conn = sqlite3.connect("shop.db")conn.row_factory = sqlite3.Rowrow = conn.execute("SELECT * FROM products WHERE name = ?", ("Mouse",)).fetchone()print(row["name"], row["price"], row["stock"])print(dict(row))conn.close()
Output
Mouse 349.0 60
{'id': 2, 'name': 'Mouse', 'category': 'electronics', 'price': 349.0, 'stock': 60}

Update and delete

Python
import sqlite3 conn = sqlite3.connect("shop.db")cur = conn.cursor()cur.execute("UPDATE products SET price = ROUND(price * 0.9, 2) WHERE category = ?", ("electronics",))print("discounted:", cur.rowcount)cur.execute("DELETE FROM products WHERE stock = 0")print("deleted:", cur.rowcount)conn.commit()print(conn.execute("SELECT name, price FROM products ORDER BY id").fetchall())conn.close()
Output
discounted: 3
deleted: 1
[('Keyboard', 1349.1), ('Mouse', 314.1), ('Notebook', 45.0), ('Monitor', 11699.1)]

Transactions

Changes become permanent only on commit(); rollback() undoes everything since the last commit. Using the connection in a with block commits on success and rolls back on an error:

Python
import sqlite3 conn = sqlite3.connect("shop.db")try:    with conn:        conn.execute("UPDATE products SET stock = stock - 5 WHERE name = 'Monitor'")        conn.execute("INSERT INTO products (name) VALUES (NULL)")   # violates NOT NULLexcept sqlite3.IntegrityError as e:    print("rolled back:", e) print(conn.execute("SELECT stock FROM products WHERE name = 'Monitor'").fetchone())conn.close()
Output
rolled back: NOT NULL constraint failed: products.name
(8,)

The stock is still 8 because the whole transaction was rolled back when the second statement failed.

Join two tables

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.executescript("""    CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);    CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL);    INSERT INTO users VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meena');    INSERT INTO orders VALUES (1, 1, 500), (2, 1, 250), (3, 3, 900);""")query = """    SELECT u.name, COUNT(o.id), COALESCE(SUM(o.amount), 0)    FROM users u LEFT JOIN orders o ON o.user_id = u.id    GROUP BY u.id ORDER BY u.name"""for row in conn.execute(query):    print(row)conn.close()
Output
('Asha', 2, 750.0)
('Meena', 1, 900.0)
('Ravi', 0, 0)

SQLite with pandas

Python
import sqlite3import pandas as pd conn = sqlite3.connect("shop.db")df = pd.read_sql_query("SELECT name, category, price FROM products", conn)print(df)print(df.groupby("category")["price"].mean().round(1).to_dict())conn.close()
Output
       name     category    price
0  Keyboard  electronics   1349.1
1     Mouse  electronics    314.1
2  Notebook   stationery     45.0
3   Monitor  electronics  11699.1
{'electronics': 4454.1, 'stationery': 45.0}

Check your understanding

Quick quiz

1. Why use ? placeholders instead of f-strings in SQL?

2. What makes inserted rows permanent?

3. Which returns a single row?

Exercise: student marks database

Create an in-memory table of students and marks, then print the average mark and the names of students above the average.

Input
("Asha", 88), ("Ravi", 64), ("Meena", 93), ("John", 71)
Expected output
Average: 79.0
Above average: ['Meena', 'Asha']
Hint 1

A subquery works: WHERE marks > (SELECT AVG(marks) FROM students).

Program Practice

20 Python SQLite Programs

Every program has a step-by-step explanation, colour-coded code and its real output.

Start practising →
Related sets:All practice sets

Python SQLite FAQ

Do I need to install SQLite for Python?

No. The sqlite3 module and the SQLite engine come with Python.

How do I see the column names of a query result?

Read cursor.description (the first item of each entry is the name), or set conn.row_factory = sqlite3.Row and call row.keys().

Is SQLite good for production?

It is excellent for local apps, tools, tests and small to medium sites with few concurrent writers. Use PostgreSQL or MySQL for many simultaneous writers.

Python JSON Module Tutorial

Python JSON Module Tutorial

Introduction

Welcome to our comprehensive guide on Python’s json module! In the world of data interchange and storage, JSON (JavaScript Object Notation) plays a pivotal role as a lightweight and human-readable format. Python’s json module equips developers with powerful tools to effortlessly handle JSON data, facilitating data serialization, deserialization, and manipulation. In this tutorial, we’ll embark on a journey through the capabilities of the json module, exploring its features, comparing it to other modules, and delving into a wide array of functions and methods with real-world examples.

Features

Python’s json module offers a range of features that make it an essential tool for working with JSON data:

  • Serialization: Convert Python objects into JSON-encoded strings.
  • Deserialization: Parse JSON-encoded strings into Python objects.
  • Human-Readable: JSON data is easily readable by both humans and machines.
  • Data Integrity: JSON ensures data integrity through structured representation.

How it is Different from Other Modules

While Python offers various modules for data manipulation and storage, the json module excels in its specialization for handling JSON data. Unlike general-purpose modules, the json module specifically addresses the challenges of working with JSON-encoded information, ensuring accurate data conversion and seamless interoperability with other systems.

Different Functions/Methods of the json Module with Examples

  1. json.dumps() – Serialize to JSON:

The dumps() function serializes Python objects to a JSON-encoded string.

				
					import json
data = {"name": "Alice", "age": 30}
json_string = json.dumps(data)
print(json_string)

				
			
  1. json.loads() – Deserialize from JSON:

The loads() function parses a JSON-encoded string into a Python object.

				
					import json
json_string = '{"name": "Alice", "age": 30}'
data = json.loads(json_string)
print(data["name"])

				
			
  1. json.dump() – Serialize to File:

The dump() function serializes Python objects to a JSON file.

				
					import json
data = {"name": "Alice", "age": 30}
with open("data.json", "w") as json_file:
    json.dump(data, json_file)

				
			
  1. json.load() – Deserialize from File:

The load() function parses a JSON file into a Python object.

				
					import json
with open("data.json", "r") as json_file:
    data = json.load(json_file)
print(data["age"])