MySQL CREATE TABLE: data types, primary keys, foreign keys and CHECK constraints

Answers — Create tables: types and constraints

1. Easy. Create a suppliers table (supplier_id INT primary key, supplier_name VARCHAR(80) NOT NULL, city VARCHAR(60)) and DESCRIBE it. Target: 3 rows: supplier_id int PRI NO; supplier_name varchar(80) NO; city varchar(60) YES

   CREATE TABLE suppliers (supplier_id INT PRIMARY KEY, supplier_name VARCHAR(80) NOT NULL, city VARCHAR(60)); DESCRIBE suppliers;

2. Easy. Create the suppliers table, insert (1, 'Paper Co', 'Ludhiana') and (2, 'Bright Lights', NULL), then list them. Target: 2 rows; Bright Lights has city NULL

   CREATE TABLE suppliers (supplier_id INT PRIMARY KEY, supplier_name VARCHAR(80) NOT NULL, city VARCHAR(60)); INSERT INTO suppliers VALUES (1, 'Paper Co', 'Ludhiana'), (2, 'Bright Lights', NULL); SELECT * FROM suppliers ORDER BY supplier_id;

3. Easy. Create categories (category_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL UNIQUE), insert Stationery, Electronics and Home without ids, and list them. Target: 3 rows: 1 Stationery, 2 Electronics, 3 Home

   CREATE TABLE categories (category_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL UNIQUE); INSERT INTO categories (name) VALUES ('Stationery'), ('Electronics'), ('Home'); SELECT * FROM categories ORDER BY category_id;

4. Medium. Add a phone column (VARCHAR(15)) to customers and show id, name and phone. Target: 6 rows; phone is NULL for everyone

   ALTER TABLE customers ADD COLUMN phone VARCHAR(15); SELECT customer_id, customer_name, phone FROM customers ORDER BY customer_id;

5. Medium. Create reviews with a status column that defaults to 'new', insert two reviews without a status, and list them. Target: 2 rows, both with status new

   CREATE TABLE reviews (review_id INT PRIMARY KEY, product_id INT NOT NULL, rating INT NOT NULL, status VARCHAR(10) NOT NULL DEFAULT 'new', FOREIGN KEY (product_id) REFERENCES products(product_id), CHECK (rating BETWEEN 1 AND 5)); INSERT INTO reviews (review_id, product_id, rating) VALUES (1, 4, 5), (2, 2, 3); SELECT * FROM reviews ORDER BY review_id;

6. Medium. With a CHECK (rating BETWEEN 1 AND 5) on reviews, try to insert a rating of 6. Record the error. Target: Error 3819: check constraint violated

   CREATE TABLE reviews (review_id INT PRIMARY KEY, product_id INT NOT NULL, rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5)); INSERT INTO reviews VALUES (1, 4, 6);

7. Medium. With a foreign key from reviews.product_id to products, try to review product 99. Record the error. Target: Error 1452: foreign key constraint fails

   CREATE TABLE reviews (review_id INT PRIMARY KEY, product_id INT NOT NULL, rating INT NOT NULL, FOREIGN KEY (product_id) REFERENCES products(product_id)); INSERT INTO reviews VALUES (1, 99, 4);

8. Medium. Insert a supplier with supplier_name NULL into a table where it is NOT NULL. Record the error. Target: Error 1048: Column 'supplier_name' cannot be null

   CREATE TABLE suppliers (supplier_id INT PRIMARY KEY, supplier_name VARCHAR(80) NOT NULL); INSERT INTO suppliers VALUES (1, NULL);

9. Hard. Create newsletter (email VARCHAR(120) UNIQUE) and insert the same email twice. Record the error. Target: Error 1062: Duplicate entry

   CREATE TABLE newsletter (signup_id INT PRIMARY KEY, email VARCHAR(120) UNIQUE); INSERT INTO newsletter VALUES (1, 'asha@example.test'), (2, 'asha@example.test');

10. Hard. Create product_suppliers with a composite primary key (product_id, supplier_id), insert (1,1), (1,2), (2,1) and list them. Target: 3 rows: (1,1), (1,2), (2,1)

    CREATE TABLE product_suppliers (product_id INT NOT NULL, supplier_id INT NOT NULL, PRIMARY KEY (product_id, supplier_id)); INSERT INTO product_suppliers VALUES (1, 1), (1, 2), (2, 1); SELECT * FROM product_suppliers ORDER BY product_id, supplier_id;

11. Hard. Use information_schema.columns to list the column names and types of products, in table order. Target: 4 rows: product_id int, product_name varchar(80), category varchar(40), price decimal(10,2)

    SELECT column_name, column_type FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'products' ORDER BY ordinal_position;

12. Professional. Design payments: payment_id auto, order_id FK, amount DECIMAL(10,2) > 0, paid_on DATE NOT NULL, method limited to upi / card / cash. Record a 300.00 UPI payment for order 101 on 2026-01-05 and show it with the customer name. Target: 1 row: 101, Asha, 300.00, upi

    CREATE TABLE payments (payment_id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL CHECK (amount > 0), paid_on DATE NOT NULL, method VARCHAR(10) NOT NULL CHECK (method IN ('upi','card','cash')), FOREIGN KEY (order_id) REFERENCES orders(order_id)); INSERT INTO payments (order_id, amount, paid_on, method) VALUES (101, 300.00, '2026-01-05', 'upi'); SELECT p.order_id, c.customer_name, p.amount, p.method FROM payments p JOIN orders o ON o.order_id = p.order_id JOIN customers c ON c.customer_id = o.customer_id;

13. Professional. Create a stock table (product_id PK and FK, on_hand INT NOT NULL DEFAULT 0 with CHECK on_hand >= 0) and fill it with one row per existing product using INSERT … SELECT. Show the row count and total stock. Target: 1 row: products_tracked = 6, total_on_hand = 0

    CREATE TABLE stock (product_id INT PRIMARY KEY, on_hand INT NOT NULL DEFAULT 0 CHECK (on_hand >= 0), FOREIGN KEY (product_id) REFERENCES products(product_id)); INSERT INTO stock (product_id) SELECT product_id FROM products; SELECT COUNT(*) AS products_tracked, SUM(on_hand) AS total_on_hand FROM stock;

14. Easy. Try to insert a product with id 1 again and record the error. Target: Error 1062: Duplicate entry '1' for key PRIMARY

    INSERT INTO products VALUES (1, 'Copy', 'Stationery', 50);

15. Medium. Try to insert an order for customer 99 and record the error. Target: Error 1452: foreign key constraint fails

    INSERT INTO orders VALUES (200, 99, '2026-04-01', 'completed');

16. Medium. Try to insert a product with price -5 and record the error. Target: Error 3819: check constraint violated

    INSERT INTO products VALUES (9, 'Broken', 'Home', -5);

17. Medium. Try an order status 'shipped' and record the error. Target: Error 3819: check constraint violated

    INSERT INTO orders VALUES (201, 1, '2026-04-01', 'shipped');