Varun sells woollen socks, mufflers and thermal vests wholesale from a godown in Ludhiana. Retailers used to phone in their orders, so he builds a small orders app on MySQL over a weekend, typing column types without much thought: rate FLOAT, phone INT, and an orders table with a plain customer_id column. The first week goes badly. Gill Traders' order should total ₹1,79,500.50, but the report shows 179500.4997253418, and a search for items priced at 38.35 finds nothing. A new retailer cannot be saved at all, because MySQL replies Out of range value for column 'phone' at row 1. Then Varun deletes an old customer, and that customer's orders stay behind, pointing at a shop that no longer exists. How should he choose column types and link his tables so that MySQL itself refuses bad data?
This post answers that in four parts. Part 1 covers data types: numbers, money, text, dates, true/false flags, ENUM and JSON. Part 2 covers primary keys, the identity of every row. Part 3 covers foreign keys, which link tables and stop orphan rows. Part 4 covers relationships (one-to-one, one-to-many, many-to-many) and the constraints NOT NULL, UNIQUE, DEFAULT and CHECK, and ends with Varun's complete schema. If you have never written a CREATE TABLE before, start with MySQL & SQL for Beginners. Every SQL sample here was run on MySQL 8.4 in its default strict mode, and the results and error messages shown are the real ones.
Part 1: MySQL Data Types
Picture a warehouse where staff throw everything into random containers: rice in water tanks, milk in cardboard boxes, GST papers mixed with vegetables. Within a week nobody can find anything, and half the stock is spoiled. A database without careful types has the same problem. A data type is the label on a column that tells MySQL what kind of value may go in it: a whole number, an exact amount of money, a short piece of text, a date. MySQL then stores the value in a suitable format, rejects values that do not fit, and knows how to sort, compare and calculate with them.
Why the Type Matters
Beginners often make every column VARCHAR because "it accepts anything". That is exactly the problem: it accepts anything, including "Ten" as a price, and it compares values as text. Here is what happens when prices are stored as text and sorted:
CREATE TABLE text_prices (item VARCHAR(50), price VARCHAR(20));
INSERT INTO text_prices VALUES ('Muffler', '129.99'), ('Socks', '38.35'), ('Blanket', '1150.00');
SELECT item, price FROM text_prices ORDER BY price;
-- +---------+---------+
-- | item | price |
-- +---------+---------+
-- | Blanket | 1150.00 |
-- | Muffler | 129.99 |
-- | Socks | 38.35 |
-- +---------+---------+
The "cheapest first" list starts with the most expensive item, because text is compared character by character and "1" comes before "3". A numeric column would sort 38.35, 129.99, 1150.00. Correct types also give you validation for free, smaller tables and indexes (an INT takes 4 bytes; the same number as text takes one byte per digit plus a length byte), and calculations such as SUM and AVG that work without conversion.
The Main Families
| Family | Common types | Typical columns |
|---|---|---|
| Integer | TINYINT, SMALLINT, INT, BIGINT | IDs, quantities, counts |
| Exact decimal | DECIMAL(p,s) | Prices, payments, tax, balances |
| Approximate | FLOAT, DOUBLE | Measurements, sensor readings, scores |
| Text | CHAR, VARCHAR, TEXT | Names, emails, phone numbers, descriptions |
| Date and time | DATE, TIME, DATETIME, TIMESTAMP | Birth dates, delivery slots, order times |
| Boolean | BOOLEAN (really TINYINT(1)) | is_active, is_paid, is_verified |
| Special | ENUM, JSON | Order status, flexible product attributes |
Integer Types: INT, BIGINT and Friends
Integer types store whole numbers: quantities, counts and, most often, ID columns. They differ only in size, and each can be signed (negative and positive) or UNSIGNED (zero and positive only, which doubles the upper limit). These are MySQL's exact limits:
| Type | Bytes | Signed range | UNSIGNED range | In words / typical use |
|---|---|---|---|---|
| TINYINT | 1 | -128 to 127 | 0 to 255 | Age, ratings, small flags |
| SMALLINT | 2 | -32768 to 32767 | 0 to 65535 | Stock of one item in a small shop |
| MEDIUMINT | 3 | -8388608 to 8388607 | 0 to 16777215 | About 1.68 crore unsigned |
| INT | 4 | -2147483648 to 2147483647 | 0 to 4294967295 | About 214 crore signed, 429 crore unsigned |
| BIGINT | 8 | -9223372036854775808 to 9223372036854775807 | 0 to 18446744073709551615 | About 9.2 × 1018 signed |
In strict mode (the default since MySQL 5.7), a value outside the range is rejected rather than silently cut down:
CREATE TABLE ranges (t TINYINT, tu TINYINT UNSIGNED, i INT, iu INT UNSIGNED, b BIGINT);
INSERT INTO ranges VALUES (127, 255, 2147483647, 4294967295, 9223372036854775807); -- OK, every maximum fits
INSERT INTO ranges (t) VALUES (128);
-- ERROR 1264 (22003): Out of range value for column 't' at row 1
INSERT INTO ranges (iu) VALUES (-1);
-- ERROR 1264 (22003): Out of range value for column 'iu' at row 1
Use INT UNSIGNED for most ID columns: about 429 crore values is plenty for customers, products and even orders of a small or medium business. Switch to BIGINT UNSIGNED for tables that can realistically pass that, such as event logs, page views or payment transactions in a large app. Changing a busy table's ID type later means rewriting the table and every foreign key that points to it, so pick with some headroom. One more detail: you may see old schemas with INT(11). The number is only a display width, it never limited the value, and MySQL has deprecated it since 8.0.17, so simply write INT.
DECIMAL for Money
DECIMAL(p,s) stores an exact number with p digits in total, s of them after the decimal point. DECIMAL(10,2) therefore holds up to 8 digits before the point and 2 after, so the largest value is 99999999.99, a little under ₹10 crore. That is the standard choice for prices, payments, GST amounts and balances, because DECIMAL does its maths in base 10, exactly the way you do on paper.
CREATE TABLE money_demo (amount DECIMAL(10,2));
INSERT INTO money_demo VALUES (99999999.99); -- OK, the largest value that fits
INSERT INTO money_demo VALUES (100000000.00);
-- ERROR 1264 (22003): Out of range value for column 'amount' at row 1
INSERT INTO money_demo VALUES (12.345); -- Query OK, 1 row affected, 1 warning
SHOW WARNINGS;
-- | Note | 1265 | Data truncated for column 'amount' at row 1 |
SELECT * FROM money_demo;
-- 99999999.99
-- 12.35 (rounded to 2 decimal places)
Notice that extra decimal places are rounded with only a note, not an error. If your app calculates 18% GST and gets ₹12.345, round it deliberately in your code or with ROUND() before inserting, so you decide the rule instead of finding it in a warning nobody reads. If you deal with very large amounts, DECIMAL(15,2) or DECIMAL(19,4) are common; DECIMAL allows up to 65 digits.
Why Not FLOAT for Money?
FLOAT (4 bytes) and DOUBLE (8 bytes) store numbers in binary, base 2. Most decimal fractions, including 0.1, 0.2 and 38.35, have no exact binary form, the same way 1/3 has no exact decimal form (0.3333…). So the stored value is a very close approximation, and the tiny errors show up when you add or compare. This is exactly what broke Varun's report:
CREATE TABLE price_test (item VARCHAR(50), price_float FLOAT, price_decimal DECIMAL(10,2));
INSERT INTO price_test VALUES ('Socks pack', 0.1, 0.1), ('Muffler', 0.2, 0.2);
SELECT SUM(price_float), SUM(price_decimal) FROM price_test;
-- +---------------------+--------------------+
-- | SUM(price_float) | SUM(price_decimal) |
-- +---------------------+--------------------+
-- | 0.30000000447034836 | 0.30 |
-- +---------------------+--------------------+
CREATE TABLE line_items (item VARCHAR(50), qty INT, rate_float FLOAT, rate_decimal DECIMAL(10,2));
INSERT INTO line_items VALUES
('Woollen socks (pair)', 1200, 38.35, 38.35),
('Muffler', 450, 129.99, 129.99),
('Thermal vest', 300, 249.95, 249.95);
SELECT SUM(qty * rate_float) AS total_float, SUM(qty * rate_decimal) AS total_decimal FROM line_items;
-- +-------------------+---------------+
-- | total_float | total_decimal |
-- +-------------------+---------------+
-- | 179500.4997253418 | 179500.50 |
-- +-------------------+---------------+
SELECT * FROM line_items WHERE rate_float = 38.35; -- Empty set
SELECT * FROM line_items WHERE rate_decimal = 38.35; -- 1 row: Woollen socks (pair)
The tricky part is that SELECT rate_float on its own displays a neat 38.35, because MySQL rounds FLOAT for display. The stored value is slightly different, which is why the equality search finds nothing and the total drifts. A FLOAT keeps only about 7 significant digits, so the errors grow with larger amounts. Interestingly, SELECT 0.1 + 0.2 in MySQL returns exactly 0.3, because MySQL treats decimal literals as exact values; written as floating-point literals, SELECT 0.1e0 + 0.2e0 returns 0.30000000000000004, the same result as in JavaScript. FLOAT and DOUBLE are fine for measurements where a tiny error does not matter, such as temperature, GPS coordinates or a rating average. For money, always use DECIMAL, or store whole paise in an integer column.
Phone Numbers, PIN Codes and Aadhaar: Store Them as Text
Varun's second bug came from treating a phone number as a number. An Indian mobile number such as 9876543210 is about 987 crore, which is larger than even INT UNSIGNED can hold. Switching to BIGINT stops the error but creates a quieter bug, because numbers have no leading zeros:
CREATE TABLE contacts_bad (name VARCHAR(50), phone INT);
INSERT INTO contacts_bad VALUES ('Gill Traders', 9876543210);
-- ERROR 1264 (22003): Out of range value for column 'phone' at row 1
CREATE TABLE contacts_bigint (name VARCHAR(50), phone BIGINT, landline BIGINT, pin INT);
INSERT INTO contacts_bigint VALUES ('Gill Traders', 9876543210, '01612345678', '141001');
SELECT * FROM contacts_bigint;
-- | Gill Traders | 9876543210 | 1612345678 | 141001 | <- the landline lost its 0
CREATE TABLE contacts_good (name VARCHAR(50), phone VARCHAR(15), landline VARCHAR(15), pin CHAR(6));
INSERT INTO contacts_good VALUES ('Gill Traders', '9876543210', '01612345678', '141001');
SELECT * FROM contacts_good;
-- | Gill Traders | 9876543210 | 01612345678 | 141001 |
The Ludhiana landline 0161-2345678 became 1612345678 in the BIGINT column, and no error told anyone. The rule is simple: if you will never do arithmetic on it, it is text. Nobody adds two phone numbers or averages PIN codes. Phone numbers also arrive as +91 98765 43210 or with an extension, which a number column cannot hold. Use VARCHAR(15) for phones (international numbers have at most 15 digits), CHAR(6) for Indian PIN codes, CHAR(15) for GSTIN and CHAR(10) for PAN. The same applies to Aadhaar (12 digits, CHAR(12)), but check first whether your business may store it at all; UIDAI rules limit who can keep full Aadhaar numbers, and most apps should keep only a masked form such as the last four digits. Validate the format (digits only, correct length) in your application or with a CHECK constraint, shown in Part 4.
CHAR vs VARCHAR vs TEXT
CHAR(n) is fixed length: every value is padded with spaces to n characters when stored, and trailing spaces are removed when you read it back. It suits values that always have the same length, such as a 3-letter city code, a PIN code or an IFSC code. VARCHAR(n) stores only the characters you give it plus a 1 or 2 byte length prefix, which makes it the default choice for names, emails and titles. In both, n counts characters, not bytes, so VARCHAR(100) holds 100 Hindi or Punjabi characters just as well as 100 English ones with MySQL's default utf8mb4 character set.
CREATE TABLE strings_demo (code CHAR(3), name VARCHAR(10), note TEXT);
INSERT INTO strings_demo VALUES ('LDH ', 'Varun ', 'ok');
SELECT CONCAT('[', code, ']') AS code, CONCAT('[', name, ']') AS name,
CHAR_LENGTH(code), CHAR_LENGTH(name) FROM strings_demo;
-- +-------+-----------+-------------------+-------------------+
-- | code | name | CHAR_LENGTH(code) | CHAR_LENGTH(name) |
-- +-------+-----------+-------------------+-------------------+
-- | [LDH] | [Varun ] | 3 | 7 |
-- +-------+-----------+-------------------+-------------------+
INSERT INTO strings_demo (name) VALUES ('Punjab Hosiery Mart');
-- ERROR 1406 (22001): Data too long for column 'name' at row 1
CHAR dropped the trailing spaces; VARCHAR kept them. And a value longer than the column is rejected in strict mode, not cut short. TEXT is for long content such as product descriptions, delivery notes or admin remarks. A TEXT column holds up to 65,535 bytes (MEDIUMTEXT about 16 MB, LONGTEXT about 4 GB). TEXT values are stored partly outside the row, can only be indexed by a prefix, and are a poor choice for anything you filter or sort by often, so keep short searchable values in VARCHAR.
| CHAR(n) | VARCHAR(n) | TEXT | |
|---|---|---|---|
| Length | Fixed, padded to n | Variable, up to n characters | Variable, up to 65,535 bytes |
| Trailing spaces | Removed on read | Kept | Kept |
| Indexing | Full | Full (within index size limits) | Prefix only |
| Good for | PIN code, IFSC, PAN, country code | Names, emails, phones, titles | Descriptions, notes, long comments |
Size VARCHAR columns sensibly. VARCHAR(5000) for a shop name costs nothing on disk, but MySQL uses the declared size when it builds temporary tables for sorting and grouping, and an index on a very long column may exceed InnoDB's index size limit. A reasonable maximum (100 for names, 255 for emails) also documents what you expect.
DATE, TIME, DATETIME and TIMESTAMP
Never store dates as text such as June 14 2026: you cannot sort it, compare it or do date arithmetic on it. MySQL has four main types, and they all take input in the format YYYY-MM-DD HH:MM:SS. Indian-style input such as '03-11-2026' is rejected with ERROR 1292 (22007): Incorrect date value: '03-11-2026' for column 'd' at row 1, so convert it in your app, or with STR_TO_DATE('03-11-2026', '%d-%m-%Y'), which returns 2026-11-03.
| Type | Stores | Example | Range | Good for |
|---|---|---|---|---|
| DATE | Date only | 2026-11-03 | 1000-01-01 to 9999-12-31 | Birth date, invoice date, expiry date |
| TIME | Time of day (or a duration) | 14:30:00 | -838:59:59 to 838:59:59 | Delivery slot, shop opening time |
| DATETIME | Date and time, as written | 2026-11-03 10:30:00 | 1000-01-01 00:00:00 to 9999-12-31 23:59:59 | Order time, scheduled dispatch, appointment |
| TIMESTAMP | A moment in time, stored as UTC | 2026-11-03 10:30:00 | 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC | Row audit columns, events that must convert across time zones |
DATETIME and TIMESTAMP look the same, but they behave differently. DATETIME stores exactly what you give it, like writing a time in a register. TIMESTAMP converts the value from the connection's time zone to UTC when storing and back to the connection's time zone when reading. Watch what happens when the same row is read from a session set to UTC:
CREATE TABLE time_demo (label VARCHAR(20), dt DATETIME, ts TIMESTAMP);
SET time_zone = '+05:30';
INSERT INTO time_demo VALUES ('order placed', '2026-11-03 10:30:00', '2026-11-03 10:30:00');
SELECT * FROM time_demo;
-- | order placed | 2026-11-03 10:30:00 | 2026-11-03 10:30:00 |
SET time_zone = '+00:00';
SELECT * FROM time_demo;
-- | order placed | 2026-11-03 10:30:00 | 2026-11-03 05:00:00 |
The DATETIME stayed at 10:30, while the TIMESTAMP now shows 05:00, the same instant in UTC. That conversion is useful when users and servers sit in different time zones, and confusing when a server's time zone changes without anyone noticing. TIMESTAMP also has a hard limit, the "year 2038 problem", because it is stored as a 4-byte count of seconds since 1970:
SET time_zone = '+05:30';
INSERT INTO time_demo (label, ts) VALUES ('far future', '2038-01-19 08:44:07'); -- OK (03:14:07 UTC)
INSERT INTO time_demo (label, ts) VALUES ('too far', '2038-01-19 08:44:08');
-- ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 08:44:08' for column 'ts' at row 1
INSERT INTO time_demo (label, dt) VALUES ('datetime ok', '2045-03-31 23:59:59'); -- OK
That limit is closer than it looks: a 15-year loan, a long warranty or a subscription end date can already pass 2038. A practical rule is to use DATETIME for business dates (order times, due dates, delivery schedules), and save them consistently, for example always in IST or always in UTC, decided once for the whole app. TIMESTAMP is still handy for audit columns, since both types support automatic values:
CREATE TABLE audit_demo (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
note VARCHAR(50),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO audit_demo (note) VALUES ('first'); -- both time columns are filled automatically
ON UPDATE CURRENT_TIMESTAMP refreshes updated_at every time the row changes, so you never have to remember to set it in code.
BOOLEAN Is Really TINYINT(1)
MySQL accepts BOOLEAN in a table definition, but it has no separate true/false type. BOOLEAN is a synonym for TINYINT(1), TRUE is 1 and FALSE is 0, as SHOW CREATE TABLE reveals:
CREATE TABLE flags (name VARCHAR(20), is_verified BOOLEAN DEFAULT FALSE);
SHOW CREATE TABLE flags;
-- CREATE TABLE `flags` (
-- `name` varchar(20) DEFAULT NULL,
-- `is_verified` tinyint(1) DEFAULT '0'
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO flags VALUES ('A', TRUE), ('B', FALSE), ('C', 5); -- 5 is accepted!
SELECT name, is_verified, is_verified IS TRUE AS truthy, is_verified = TRUE AS equals_true FROM flags;
-- +------+-------------+--------+-------------+
-- | name | is_verified | truthy | equals_true |
-- +------+-------------+--------+-------------+
-- | A | 1 | 1 | 1 |
-- | B | 0 | 0 | 0 |
-- | C | 5 | 1 | 0 |
-- +------+-------------+--------+-------------+
Because any small number fits, a bug can store 5, and then WHERE is_verified = TRUE misses that row, since it really means = 1. Write WHERE is_verified or WHERE is_verified IS TRUE, or add CHECK (is_verified IN (0, 1)). Use BOOLEAN for yes/no flags such as is_paid, is_active and is_verified, and name them so the meaning of 1 is obvious.
ENUM, Briefly
ENUM limits a column to a fixed list of strings, which suits short, stable lists such as an order status. MySQL stores the position in the list (1, 2, 3…) internally, so it is compact, and in strict mode an unknown value is rejected:
CREATE TABLE enum_demo (
order_id INT,
status ENUM('pending','packed','shipped','delivered') NOT NULL DEFAULT 'pending'
);
INSERT INTO enum_demo (order_id) VALUES (1); -- status = 'pending'
INSERT INTO enum_demo VALUES (2, 'shipped'); -- OK
INSERT INTO enum_demo VALUES (3, 'dispatched');
-- ERROR 1265 (01000): Data truncated for column 'status' at row 1
The error text is unhelpful, so remember that "Data truncated" on an ENUM column means "not in the list". The downsides: adding a new value needs an ALTER TABLE, ORDER BY status sorts by list position rather than alphabetically, and you cannot attach extra details (like a display label in Hindi) to a value. If the list changes often or needs extra columns, use a small lookup table with a foreign key instead.
JSON, Briefly
A JSON column stores a JSON document. MySQL checks it is valid on insert and lets you read parts of it, which is handy for attributes that differ by product, such as size for a vest and fabric for a muffler:
CREATE TABLE json_demo (product_id INT, attrs JSON);
INSERT INTO json_demo VALUES (1, '{"size": "XL", "colour": "maroon", "fabric": "wool"}');
INSERT INTO json_demo VALUES (2, '{size: XL}');
-- ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'json_demo.attrs'.
SELECT product_id, attrs->>'$.colour' AS colour FROM json_demo;
-- | 1 | maroon |
->> extracts a value and removes the JSON quotes; JSON_EXTRACT(attrs, '$.colour') returns "maroon" with the quotes still on. Use JSON for genuinely flexible, optional data. Anything you join on, filter by constantly or must keep consistent (price, stock, customer ID) belongs in a normal typed column, where types, foreign keys and CHECK constraints protect it.
Choosing Types for Real Tables
Here is how the types come together for Varun's main tables. The full CREATE TABLE statements, with keys and constraints, are at the end of Part 4.
| Column | Type | Why |
|---|---|---|
| customer_id, product_id, order_id | INT UNSIGNED AUTO_INCREMENT | Whole numbers, never negative, generated by MySQL |
| shop_name, product_name | VARCHAR(100) | Short text of varying length |
| phone | VARCHAR(15) | Not used in maths; keeps leading zeros and +91 |
| pin_code / gstin / pan | CHAR(6) / CHAR(15) / CHAR(10) | Fixed-length codes |
| rate, order totals | DECIMAL(10,2) | Exact money |
| qty | INT UNSIGNED | Whole, non-negative count |
| description | TEXT | Long, rarely filtered |
| is_active | BOOLEAN | Yes/no flag |
| status | ENUM(...) | Short fixed list |
| ordered_at, created_at | DATETIME DEFAULT CURRENT_TIMESTAMP | Business time, no 2038 limit |
| attributes | JSON | Optional details that vary by product |
JavaScript developers will recognise the idea from Chapter 3: Variables, Data Types and Memory, where JavaScript's single number type has the same 0.1 + 0.2 problem as FLOAT. The difference is that MySQL makes you choose a type up front and then enforces it on every insert.
Part 2: Primary Keys
Suppose a retailer calls Varun and says, "I want to check my order for mufflers." Dozens of orders contain mufflers. "Which shop?" "Sharma Hosiery." There are two shops with that name, in different markets. Names, products and dates all repeat, so none of them can point to exactly one order. What the call needs is an order number that belongs to one order and nothing else. In a database, that number is the primary key.
It works like a government ID number. Thousands of people share the name Harpreet Singh, but each person's ID number is unique, so an office can always find the right record. A primary key is a column (or a combination of columns) whose value uniquely identifies each row in a table. Every table should have one, and a table can have only one.
The Rules of a Primary Key
A primary key must be unique and NOT NULL, and MySQL enforces both. It should also be stable, meaning it never changes once assigned, because other tables will store it as a reference. A shop name can change from "Fresh Knit" to "Fresh Knit International", and an email address can change too, so they make poor primary keys. Here MySQL enforces the first two rules:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10,2)
);
INSERT INTO products VALUES
(1, 'Woollen socks (pair)', 38.35),
(2, 'Muffler', 129.99),
(3, 'Thermal vest', 249.95);
INSERT INTO products VALUES (1, 'Monkey cap', 75.00);
-- ERROR 1062 (23000): Duplicate entry '1' for key 'products.PRIMARY'
INSERT INTO products VALUES (NULL, 'Monkey cap', 75.00);
-- ERROR 1048 (23000): Column 'product_id' cannot be null
In Node.js with the mysql2 driver, these arrive as err.code === 'ER_DUP_ENTRY' (errno 1062) and 'ER_BAD_NULL_ERROR' (1048). Checking err.code is how an API turns a duplicate into a friendly 409 Conflict instead of a crash.
AUTO_INCREMENT
Handing out IDs by hand is error-prone: two staff members adding products at the same moment could both pick 4. AUTO_INCREMENT makes MySQL assign the next number itself, safely, even when many connections insert at once:
CREATE TABLE products (
product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
INSERT INTO products (product_name, price) VALUES ('Woollen socks (pair)', 38.35);
INSERT INTO products (product_name, price) VALUES ('Muffler', 129.99), ('Thermal vest', 249.95);
SELECT LAST_INSERT_ID(); -- 2 (the FIRST id of the last multi-row insert)
DELETE FROM products WHERE product_id = 3;
INSERT INTO products (product_name, price) VALUES ('Monkey cap', 75.00);
SELECT * FROM products;
-- +------------+----------------------+--------+
-- | product_id | product_name | price |
-- +------------+----------------------+--------+
-- | 1 | Woollen socks (pair) | 38.35 |
-- | 2 | Muffler | 129.99 |
-- | 4 | Monkey cap | 75.00 |
-- +------------+----------------------+--------+
Two details surprise beginners. First, LAST_INSERT_ID() after a multi-row insert returns the first ID of that batch (2 here), not the last. Second, IDs have gaps: the deleted ID 3 is never reused, and even an insert that fails (for example on a duplicate UNIQUE value) can use up a number. That is by design, because reusing numbers would be slow and dangerous. So never use AUTO_INCREMENT IDs as invoice numbers, which under GST rules must be consecutive; generate those separately.
Natural Keys vs Surrogate Keys
A natural key is a value that already exists in the real world and happens to be unique: an email, a GSTIN, a PAN, a phone number. A surrogate key is an artificial ID with no business meaning, such as customer_id = 101, created only to identify the row.
| Natural key (email, GSTIN) | Surrogate key (customer_id) | |
|---|---|---|
| Can it change? | Yes: emails change, a business can get a new GSTIN | Never |
| Size | Often long text (15 to 255 characters) | 4 or 8 bytes |
| Always available? | Not always: some retailers have no GSTIN | Yes, MySQL generates it |
| Exposes personal data? | Yes, when it appears in URLs and other tables | No |
Most modern systems use a surrogate primary key and still protect the natural value with a UNIQUE constraint, so the database rejects two customers with the same phone or GSTIN. You get a stable, compact identity and no duplicates.
Composite Primary Keys
A primary key can span several columns. In an order_items table, the pair (order_id, product_id) is unique because the same product should appear only once per order (with a quantity). Declare it with PRIMARY KEY (order_id, product_id). A duplicate pair is rejected as Duplicate entry '2-2' for key 'order_items.PRIMARY'. You will see the full table in Part 4.
How InnoDB Stores Rows by Primary Key
InnoDB, MySQL's default storage engine, physically stores each table's rows sorted by the primary key, in a tree structure called the clustered index. Think of a register where pages are kept in order of customer ID: finding customer 1042 means jumping straight to the right page, which is why WHERE customer_id = 1042 is about the fastest lookup MySQL can do. Every other index (on phone, on city) stores the primary key value next to its own value, and uses it to fetch the full row.
Three practical consequences follow. A short primary key keeps every other index small, because each index entry carries a copy of it. An increasing key such as AUTO_INCREMENT adds new rows at the end of the tree, while random keys such as UUID strings insert into the middle and cause extra page splitting. And if you create no primary key, InnoDB picks the first UNIQUE NOT NULL index, or failing that adds a hidden 6-byte row ID that you cannot use in queries, so it is always better to declare one yourself.
Primary Keys Make CRUD Precise
With a primary key, every read, update and delete can target exactly one row. Without one, UPDATE products SET price = 134.99 WHERE product_name = 'Muffler' changes every muffler, including ones you did not mean to touch.
INSERT INTO products (product_name, price) VALUES ('Woollen gloves', 95.00); -- Create: MySQL assigns id 5
SELECT * FROM products WHERE product_id = 2; -- Read: exactly one row (Muffler)
UPDATE products SET price = 134.99 WHERE product_id = 2; -- Update: Rows matched: 1 Changed: 1
DELETE FROM products WHERE product_id = 5; -- Delete: 1 row affected
Part 3: Foreign Keys
A primary key identifies a row. A foreign key is a column in one table that stores the primary key of a row in another table, which creates a link between them. In Varun's app, each row in orders stores a customer_id. That number is the customer's primary key in customers, so orders.customer_id is a foreign key. The table that holds the foreign key is called the child (orders), and the table it points to is the parent (customers), much like a child's school form that records the father's ID.
Storing the ID instead of the shop name is also better design. If Gill Traders places 500 orders, writing "Gill Traders" 500 times wastes space, and when the shop is renamed you must fix 500 rows and will miss some. Storing customer_id = 1 keeps the name in one place.
What Happens Without a Foreign Key
A column named customer_id is just a number until you declare it a foreign key. This is the exact situation that left orphan orders in Varun's first version:
CREATE TABLE customers_nofk (
customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
shop_name VARCHAR(100) NOT NULL
);
CREATE TABLE orders_nofk (
order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
total DECIMAL(10,2) NOT NULL
);
INSERT INTO customers_nofk (shop_name) VALUES ('Gill Traders'), ('Sharma Hosiery');
INSERT INTO orders_nofk (customer_id, total) VALUES (1, 46020.00), (1, 58495.50), (2, 74985.00);
DELETE FROM customers_nofk WHERE customer_id = 1; -- succeeds, no complaint
SELECT o.order_id, o.customer_id, c.shop_name, o.total
FROM orders_nofk o LEFT JOIN customers_nofk c ON c.customer_id = o.customer_id;
-- +----------+-------------+----------------+----------+
-- | order_id | customer_id | shop_name | total |
-- +----------+-------------+----------------+----------+
-- | 1 | 1 | NULL | 46020.00 |
-- | 2 | 1 | NULL | 58495.50 |
-- | 3 | 2 | Sharma Hosiery | 74985.00 |
-- +----------+-------------+----------------+----------+
INSERT INTO orders_nofk (customer_id, total) VALUES (999, 500.00); -- also succeeds: customer 999 never existed
Orders 1 and 2 are now orphans: they belong to a customer who does not exist. Reports drop them or show blank names, outstanding payments cannot be chased, and nobody knows who ordered ₹1,04,515.50 worth of stock.
Creating a Foreign Key
Declare the link with FOREIGN KEY (column) REFERENCES parent_table (column). Giving the constraint a name with CONSTRAINT makes error messages and later changes easier to read:
CREATE TABLE customers (
customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
shop_name VARCHAR(100) NOT NULL,
phone VARCHAR(15) NOT NULL UNIQUE
);
CREATE TABLE orders (
order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
total DECIMAL(10,2) NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
INSERT INTO customers (shop_name, phone) VALUES ('Gill Traders', '9876543210'), ('Sharma Hosiery', '9814012345');
INSERT INTO orders (customer_id, total) VALUES (1, 46020.00), (1, 58495.50), (2, 74985.00);
INSERT INTO orders (customer_id, total) VALUES (999, 500.00);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`wholesale`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))
MySQL now checks every insert and update on orders: the customer_id must exist in customers. In Node.js the error code is ER_NO_REFERENCED_ROW_2 (errno 1452). This guarantee is called referential integrity, a heavy term for a simple promise: every reference points to something real.
fk_orders_customer and repeats the step that created orphans last time, deleting Gill Traders while its orders still exist. This time MySQL refuses: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`wholesale`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)). In Node.js this is ER_ROW_IS_REFERENCED_2, and SELECT COUNT(*) FROM orders still returns 3. Varun realises he never wanted to erase a shop with order history in the first place; he only wanted it off the active list. He adds an is_active BOOLEAN NOT NULL DEFAULT TRUE column and runs UPDATE customers SET is_active = FALSE WHERE customer_id = 1 instead. The orders, the amounts and the shop's name all stay intact for his accounts.A few requirements trip people up when creating foreign keys. Both tables must use InnoDB (the default). The child column and the parent column must have compatible types, including the same signedness; pointing an INT at an INT UNSIGNED fails with ERROR 3780 (HY000): Referencing column 'parent_id' and referenced column 'id' in foreign key constraint 'fk_child_parent' are incompatible. The parent column should be the primary key or a UNIQUE column. MySQL automatically creates an index on the child column if none exists (SHOW CREATE TABLE orders lists KEY `fk_orders_customer` (`customer_id`)), which keeps the checks and later joins fast.
Finding Orphans Before Adding a Foreign Key
If the table already contains orphans, adding the foreign key later fails, because MySQL checks existing rows too. Find the orphans first with a LEFT JOIN, then fix or archive them:
-- On the orders_nofk / customers_nofk tables from above
SELECT o.order_id, o.customer_id, o.total
FROM orders_nofk o
LEFT JOIN customers_nofk c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- +----------+-------------+----------+
-- | order_id | customer_id | total |
-- +----------+-------------+----------+
-- | 1 | 1 | 46020.00 |
-- | 2 | 1 | 58495.50 |
-- | 4 | 999 | 500.00 |
-- +----------+-------------+----------+
ALTER TABLE orders_nofk
ADD CONSTRAINT fk_orders_nofk_customer
FOREIGN KEY (customer_id) REFERENCES customers_nofk (customer_id);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`wholesale`.`#sql-88f4_8`, CONSTRAINT `fk_orders_nofk_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers_nofk` (`customer_id`))
(The #sql-… name is the temporary copy MySQL builds while altering the table; it differs on every run.) This is why adding foreign keys from day one is far easier than adding them after months of data.
ON DELETE and ON UPDATE Rules
When a parent row is deleted or its key changes, MySQL needs to know what to do with the child rows. You choose with ON DELETE and ON UPDATE clauses:
| Rule | What happens to child rows | Good for |
|---|---|---|
RESTRICT / NO ACTION (default) | The parent change is refused while children exist (error 1451) | Customers with orders, products in past orders: anything with business history |
CASCADE | Children are deleted (or updated) along with the parent | Rows that mean nothing alone: order items of an order, a supplier's bank details |
SET NULL | The child's foreign key becomes NULL; the child row stays | Optional links: a product whose supplier is removed but which you still sell |
In InnoDB, RESTRICT and NO ACTION behave the same, and leaving out ON DELETE gives you that behaviour. (MySQL also parses SET DEFAULT, but InnoDB rejects it, so ignore it.) Here are CASCADE, SET NULL and ON UPDATE CASCADE together, with a suppliers table:
CREATE TABLE suppliers (
supplier_id INT UNSIGNED PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE supplier_products (
sp_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
supplier_id INT UNSIGNED, -- nullable, required for SET NULL
product_name VARCHAR(100) NOT NULL,
FOREIGN KEY (supplier_id) REFERENCES suppliers (supplier_id)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE supplier_bank_accounts (
supplier_id INT UNSIGNED PRIMARY KEY,
ifsc CHAR(11) NOT NULL,
FOREIGN KEY (supplier_id) REFERENCES suppliers (supplier_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO suppliers VALUES (101, 'Oswal Knit Works'), (102, 'Tiruppur Cotton Mills');
INSERT INTO supplier_products (supplier_id, product_name) VALUES (101, 'Muffler'), (101, 'Thermal vest'), (102, 'Cotton vest');
INSERT INTO supplier_bank_accounts VALUES (101, 'PUNB0123400'), (102, 'SBIN0001234');
-- ON UPDATE CASCADE: change the parent key, children follow
UPDATE suppliers SET supplier_id = 5001 WHERE supplier_id = 101;
SELECT * FROM supplier_products;
-- | 1 | 5001 | Muffler |
-- | 2 | 5001 | Thermal vest |
-- | 3 | 102 | Cotton vest |
-- Delete the parent: SET NULL keeps products, CASCADE removes the bank row
DELETE FROM suppliers WHERE supplier_id = 5001;
SELECT * FROM supplier_products;
-- | 1 | NULL | Muffler |
-- | 2 | NULL | Thermal vest |
-- | 3 | 102 | Cotton vest |
SELECT * FROM supplier_bank_accounts;
-- | 102 | SBIN0001234 |
SET NULL only works on a nullable column. Declaring the child column NOT NULL with ON DELETE SET NULL fails at CREATE TABLE time with ERROR 1830 (HY000): Column 'parent_id' cannot be NOT NULL: needed in a foreign key constraint 'fk_b_parent' SET NULL. ON UPDATE CASCADE is rarely needed when you use surrogate keys, because those never change; it matters more when the key is a natural value such as a code.
Use CASCADE with care. One DELETE on a parent can silently remove thousands of rows in several tables, and cascades can chain (customer → orders → order items). Reserve it for child rows that genuinely mean nothing without the parent, and prefer RESTRICT plus a "soft delete" flag, as Varun did, for anything with financial or legal history.
Primary Key vs Foreign Key
| Primary key | Foreign key | |
|---|---|---|
| Purpose | Identifies each row in its own table | Points to a row in another (or the same) table |
| Uniqueness | Always unique | Repeats freely (one customer, many orders) |
| NULL allowed? | Never | Yes, unless declared NOT NULL |
| How many per table? | Exactly one (can span several columns) | As many as you need |
| Index | Is the clustered index in InnoDB | MySQL creates an index if none exists |
| Example | customers.customer_id | orders.customer_id |
| Short version | Identity | Relationship |
Part 4: Database Relationships
Foreign keys are the tool; relationships are the patterns you build with them. Nothing in a business exists alone. A customer places orders, an order contains products, a product comes from a supplier. Before creating tables, ask for each pair of things: "How many of B can one A have, and how many A can one B have?" That count is called cardinality, and the answer is always one of three patterns.
One-to-One (1:1)
In a one-to-one relationship, a row in table A matches at most one row in table B, and the reverse. A person and their passport is the classic example. In Varun's app, each retailer may have one KYC record holding GSTIN, PAN and a verification date. Why not just add those columns to customers? Because the data is optional (not every small retailer is GST-registered), sensitive (you may restrict who can read it), and changes on its own schedule. Splitting it keeps the main table lean and lets you grant access to customer_kyc separately.
The simplest way to enforce 1:1 is to make the child's primary key also its foreign key, so each customer can appear only once:
CREATE TABLE customer_kyc (
customer_id INT UNSIGNED PRIMARY KEY,
gstin CHAR(15) NOT NULL UNIQUE,
pan CHAR(10) NOT NULL,
verified_on DATE,
CONSTRAINT fk_kyc_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE
);
INSERT INTO customer_kyc VALUES (1, '03ABCDE1234F1Z5', 'ABCDE1234F', '2026-10-15');
INSERT INTO customer_kyc VALUES (1, '03ZZZZZ9999Z1Z9', 'ZZZZZ9999Z', NULL);
-- ERROR 1062 (23000): Duplicate entry '1' for key 'customer_kyc.PRIMARY'
An alternative is a separate kyc_id primary key plus a UNIQUE foreign key column; both enforce "one per customer". (The GSTIN and PAN above are dummy values.)
One-to-Many (1:N)
This is the most common relationship by far. One customer places many orders, but each order belongs to exactly one customer. One supplier supplies many products; one category contains many products; one order has many payments when a retailer pays in instalments. The rule for building it is always the same: the foreign key goes on the "many" side. That is why orders holds customer_id, and the same customer ID repeats on every order that shop places. Repetition here is normal and correct; the shop's details still live in one row.
Many-to-Many (M:N) and the Junction Table
One order contains many products (socks, mufflers, vests), and one product appears in many orders. Neither table can hold the foreign key, because neither side is "one". A student and courses work the same way: one student takes many courses, and one course has many students. The solution is a third table, called a junction, bridge or mapping table, whose rows each record one pairing. In Varun's app that is order_items:
CREATE TABLE order_items (
order_id INT UNSIGNED NOT NULL,
product_id INT UNSIGNED NOT NULL,
qty INT UNSIGNED NOT NULL,
rate DECIMAL(10,2) NOT NULL, -- price at the time of the order
PRIMARY KEY (order_id, product_id), -- composite key: each product once per order
CONSTRAINT fk_items_order FOREIGN KEY (order_id)
REFERENCES orders (order_id) ON DELETE CASCADE,
CONSTRAINT fk_items_product FOREIGN KEY (product_id)
REFERENCES products (product_id) ON DELETE RESTRICT,
CONSTRAINT chk_qty_positive CHECK (qty > 0)
);
A junction table is more than two IDs. It is the natural home for facts about the pairing: the quantity, and the rate charged on that day. Copying the rate here matters, because when Varun raises the muffler price next winter, old invoices must still show the old price. The two foreign keys use different rules on purpose: deleting an order removes its items (CASCADE), but a product that appears in any order cannot be deleted (RESTRICT), since that would damage sales history.
The beginner alternative, an orders table with columns product1, product2, product3, or one column holding 'Socks,Muffler,Vest', breaks as soon as an order has more items than columns, and makes questions like "how many mufflers did we sell in November?" painful. With a junction table, that question is a simple SUM.
| Relationship | Example | How to build it |
|---|---|---|
| One-to-one (1:1) | Customer and KYC record | Foreign key that is also the primary key (or UNIQUE) in the child |
| One-to-many (1:N) | Customer and orders | Foreign key in the "many" table |
| Many-to-many (M:N) | Orders and products | Junction table with two foreign keys and a composite primary key |
Constraints: NOT NULL, UNIQUE, DEFAULT and CHECK
Types decide the kind of value; constraints add business rules on top, and MySQL enforces them for every app, script and admin who touches the database, not just your main API.
- NOT NULL means the column must have a value. Use it on everything that is required, such as shop name, phone and rate. Without it, a missing value becomes
NULL, andNULLbehaves oddly in comparisons and sums. InsertingNULLinto a NOT NULL column fails withERROR 1048 (23000): Column 'qty' cannot be null. - UNIQUE stops duplicates in a non-key column. With
phone VARCHAR(15) NOT NULL UNIQUE, a second shop with the same number fails withERROR 1062 (23000): Duplicate entry '9876543210' for key 'customers.phone'. With the default collation (utf8mb4_0900_ai_ci) text comparison ignores case, so'Varun' = 'varun'is true and a UNIQUE email column treats those as the same. UNIQUE columns may contain several NULLs, because NULL is not equal to anything. - DEFAULT supplies a value when the insert leaves the column out:
city VARCHAR(50) NOT NULL DEFAULT 'Ludhiana',is_active BOOLEAN NOT NULL DEFAULT TRUE,created_at DATETIME DEFAULT CURRENT_TIMESTAMP. - CHECK enforces any simple rule on a row, such as "stock cannot go negative" or "rate must be above zero".
CREATE TABLE stock (
product_id INT UNSIGNED PRIMARY KEY,
qty INT NOT NULL DEFAULT 0,
CONSTRAINT chk_qty_not_negative CHECK (qty >= 0)
);
INSERT INTO stock (product_id) VALUES (1); -- qty defaults to 0
INSERT INTO stock VALUES (2, -5);
-- ERROR 3819 (HY000): Check constraint 'chk_qty_not_negative' is violated.
UPDATE stock SET qty = qty - 10 WHERE product_id = 1; -- selling 10 that you don't have
-- ERROR 3819 (HY000): Check constraint 'chk_qty_not_negative' is violated.
INSERT INTO stock VALUES (3, NULL);
-- ERROR 1048 (23000): Column 'qty' cannot be null
A history note that still catches people: MySQL accepted CHECK syntax for years but ignored it. CHECK constraints are enforced only from MySQL 8.0.16 onwards. On MySQL 5.7 or an older 8.0, the table above would happily store -5. Run SELECT VERSION(); before relying on them, and keep validating input in your application as well, since the database check is your last line of defence, not your only one.
Putting It Together: Varun's Schema
Here is Varun's rebuilt database with every idea from this post: the right types, surrogate primary keys, natural values protected by UNIQUE, foreign keys with deliberate ON DELETE rules, a 1:1 table, a 1:N link, an M:N junction table and CHECK constraints. Run it on a fresh database (the table names overlap with the demos above) on MySQL 8.0.16 or later.
CREATE TABLE customers (
customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
shop_name VARCHAR(100) NOT NULL,
city VARCHAR(50) NOT NULL DEFAULT 'Ludhiana',
phone VARCHAR(15) NOT NULL UNIQUE,
pin_code CHAR(6) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE customer_kyc (
customer_id INT UNSIGNED PRIMARY KEY,
gstin CHAR(15) NOT NULL UNIQUE,
pan CHAR(10) NOT NULL,
verified_on DATE,
CONSTRAINT fk_kyc_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE
);
CREATE TABLE products (
product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
rate DECIMAL(10,2) NOT NULL,
attributes JSON,
CONSTRAINT chk_rate_positive CHECK (rate > 0)
);
CREATE TABLE orders (
order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
status ENUM('pending','packed','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending',
ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE RESTRICT
);
-- order_items exactly as in the many-to-many section above
INSERT INTO customers (shop_name, phone, pin_code) VALUES
('Gill Traders', '9876543210', '141001'),
('Sharma Hosiery', '9814012345', '141008');
INSERT INTO products (product_name, rate, attributes) VALUES
('Woollen socks (pair)', 38.35, '{"size": "free", "colour": "grey"}'),
('Muffler', 129.99, '{"colour": "maroon", "fabric": "wool"}'),
('Thermal vest', 249.95, '{"size": "XL"}');
INSERT INTO orders (customer_id) VALUES (1), (2);
INSERT INTO order_items VALUES
(1, 1, 1200, 38.35),
(1, 2, 450, 129.99),
(1, 3, 300, 249.95),
(2, 2, 40, 129.99);
SELECT o.order_id, c.shop_name, o.status, SUM(oi.qty * oi.rate) AS order_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.shop_name, o.status;
-- +----------+----------------+---------+-------------+
-- | order_id | shop_name | status | order_total |
-- +----------+----------------+---------+-------------+
-- | 1 | Gill Traders | pending | 179500.50 |
-- | 2 | Sharma Hosiery | pending | 5199.60 |
-- +----------+----------------+---------+-------------+
Gill Traders' total is now exactly ₹1,79,500.50. The database also refuses each of Varun's old mistakes on its own:
INSERT INTO order_items VALUES (2, 3, 0, 249.95);
-- ERROR 3819 (HY000): Check constraint 'chk_qty_positive' is violated.
INSERT INTO order_items VALUES (2, 2, 10, 129.99);
-- ERROR 1062 (23000): Duplicate entry '2-2' for key 'order_items.PRIMARY'
INSERT INTO products (product_name, rate) VALUES ('Free sample', 0);
-- ERROR 3819 (HY000): Check constraint 'chk_rate_positive' is violated.
INSERT INTO orders (customer_id, status) VALUES (1, 'dispatched');
-- ERROR 1265 (01000): Data truncated for column 'status' at row 1
DELETE FROM customers WHERE customer_id = 1;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`wholesale`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE RESTRICT)
DELETE FROM orders WHERE order_id = 2; -- OK: its order_items row is removed by CASCADE
DELETE FROM customers WHERE customer_id = 2; -- OK now: Sharma Hosiery has no orders left
That final query used JOIN to combine three tables. Joins deserve a full post of their own, and so does making them fast with indexes: that is the next post, Master MySQL Joins, Indexing, Query Optimization & ACID Transactions. For a larger design with vendors, categories, carts and payments built on these same patterns, see How to Design a Scalable E-Commerce & Multi-Vendor Marketplace Database.
Common Mistakes (and the Fix)
| Mistake | What goes wrong | Fix |
|---|---|---|
| FLOAT or DOUBLE for prices | Totals like 179500.4997253418; WHERE rate = 38.35 finds nothing | DECIMAL(10,2), or whole paise in an integer column |
| INT or BIGINT for phone numbers, PIN codes, Aadhaar | Out-of-range errors, lost leading zeros, no room for +91 | VARCHAR(15) / CHAR(6) / CHAR(12), validated by format |
| VARCHAR for everything (prices, quantities, dates) | Wrong sorting (1150 before 129), no validation, conversions in every query | A proper numeric or date type for each column |
| No foreign keys "for flexibility" | Orphan orders and references to customers that never existed | Declare foreign keys from day one; find existing orphans with a LEFT JOIN ... IS NULL |
| CASCADE on everything | One delete silently wipes orders and history across tables | RESTRICT for business records, CASCADE only for true child details, a soft-delete flag for "remove from the list" |
| Names or emails as primary keys; tables without any primary key | Duplicates, painful renames, slow lookups, a hidden row ID you cannot use | INT UNSIGNED AUTO_INCREMENT primary key plus UNIQUE on the natural value |
My Opinion
Spend ten minutes on types before writing any code. Columns are cheap to choose and expensive to change once data and other tables depend on them. Write the column list on paper, ask "will I ever do maths on this?" and "can this be missing?" for each one, and most bad choices disappear.
DECIMAL for money, no exceptions. There is no situation in a business app where FLOAT's speed or size is worth an invoice that is off by a fraction of a paisa. If your team prefers integers, store paise in a BIGINT, but pick one approach and use it everywhere.
Always declare foreign keys, and default to RESTRICT. Some teams skip them for speed or convenience. In my view the cost of orphan data, found months later in a financial report, is far higher than the small cost of the checks. Let the database say no, and decide deliberately where CASCADE is safe.
Prefer DATETIME for business dates and pick one time zone convention. TIMESTAMP's automatic conversion and its 2038 limit cause more confusion than they save. Store times consistently and convert at the edge, in the API or the screen.
Use CHECK and NOT NULL generously on MySQL 8.0.16+. Application validation can be bypassed by a script, a bulk import or a hurried admin query. Constraints are a few words in CREATE TABLE and protect every path into the data.
Try It Yourself
These combine the original post's mini exercises. Write your answers first, then open the answers. Questions 5 to 7 can be checked by running them on any MySQL 8.0.16+ server.
1. Choose a MySQL type for each column of a suppliers table: supplier name,
email, product price, stock quantity, order date and time, payment status
(paid / unpaid), and a long description.
2. Why is VARCHAR(15) better than BIGINT for a mobile number? Give two reasons.
3. Pick the best primary key for each table:
users (name, email, user_id), products (product_name, product_id),
orders (order_date, order_id).
4. In tables vendors(vendor_id, name) and products(product_id, name, vendor_id),
which column is the foreign key, and which table is the parent?
5. Name the relationship type: vendor and products; retailer and orders;
orders and products; vendor and its verification record.
6. What does SELECT SUM(x) return for a FLOAT column holding 0.1 and 0.2,
and what does the same query return for DECIMAL(10,2)?
7. A child column is declared INT NOT NULL with ON DELETE SET NULL. What happens?
8. You must store an order total of ₹12,49,999.75. Is DECIMAL(8,2) enough?
Show answers
1. name VARCHAR(100), email VARCHAR(255) UNIQUE, price DECIMAL(10,2),
stock INT UNSIGNED (or INT with CHECK (stock >= 0)), order date/time DATETIME,
payment status BOOLEAN (is_paid) or an ENUM if more states may come,
description TEXT.
2. Any two of: a 10-digit mobile number is out of range for INT; numbers lose
leading zeros (0161... becomes 161...); you never do arithmetic on it;
a number column cannot hold "+91" or spaces.
3. user_id, product_id, order_id (stable surrogate keys). Put UNIQUE on email.
4. products.vendor_id is the foreign key; vendors is the parent table.
5. One-to-many; one-to-many; many-to-many (through order_items); one-to-one.
6. FLOAT: 0.30000000447034836. DECIMAL(10,2): 0.30.
7. CREATE TABLE fails with ERROR 1830: Column ... cannot be NOT NULL:
needed in a foreign key constraint ... SET NULL.
8. No. DECIMAL(8,2) allows 6 digits before the point (max 999999.99);
1249999.75 needs 7, and MySQL rejects it with ERROR 1264 Out of range.
Use DECIMAL(10,2) or larger.
Frequently Asked Questions
Should I use INT or BIGINT for primary keys?
INT UNSIGNED holds about 429 crore values, enough for most tables in small and medium apps. Use BIGINT UNSIGNED for tables that grow without limit, such as logs, events or transactions in a large system, or when you simply do not want to think about it again. The extra 4 bytes per row (and per index entry) rarely matter at small scale.
Can I use UUIDs as primary keys?
You can, and they help when IDs are generated outside the database or must not be guessable. As text (CHAR(36)) they are large and random, which makes every index bigger and inserts land all over the clustered index. If you need them, store them as BINARY(16) with MySQL's UUID_TO_BIN(UUID(), 1), which also reorders the time part so new values are roughly increasing, or keep an AUTO_INCREMENT key internally and expose a UUID as a separate UNIQUE column.
Do foreign keys slow MySQL down?
Each insert or update on the child table does an extra index lookup in the parent, and deletes on the parent check the child. That cost is small, and the automatic index on the foreign key column usually speeds up the joins you run anyway. Some very large systems drop foreign keys for sharding or bulk loading, but for a normal application the safety is worth far more than the overhead.
Is ENUM or a lookup table better for status values?
ENUM is fine for short lists that rarely change, such as order status. Use a lookup table (order_statuses with an ID and a name) when admins should add values without a schema change, when you need extra details per value such as a display label or sort order, or when other tables also refer to the same list.
Key Takeaways
- Pick the narrowest type that safely fits: INT UNSIGNED for most IDs and counts, BIGINT for very large tables, DECIMAL for money, VARCHAR for short text, TEXT for long text.
- FLOAT and DOUBLE are approximate. In MySQL a FLOAT sum of 0.1 and 0.2 gives 0.30000000447034836; DECIMAL gives 0.30.
- Phone numbers, PIN codes, GSTIN, PAN and Aadhaar are text: no maths, leading zeros matter.
- DATETIME stores what you give it; TIMESTAMP converts through UTC and ends on 2038-01-19 03:14:07 UTC. BOOLEAN is TINYINT(1).
- A primary key is unique, NOT NULL and stable. InnoDB stores rows in primary key order, so short, increasing AUTO_INCREMENT keys work best.
- A foreign key makes MySQL reject references to missing rows (error 1452) and protect parents that still have children (error 1451). Choose RESTRICT, CASCADE or SET NULL deliberately.
- One-to-many puts the foreign key on the "many" side; many-to-many needs a junction table with a composite primary key.
- NOT NULL, UNIQUE, DEFAULT and CHECK (enforced from MySQL 8.0.16) turn business rules into database rules.
Next: combine these tables with joins and make the queries fast with indexes in Master MySQL Joins, Indexing, Query Optimization & ACID Transactions.
References
- MySQL 8.4 Reference Manual: Data Types
- MySQL 8.4 Reference Manual: Integer Types (exact ranges)
- MySQL 8.4 Reference Manual: FOREIGN KEY Constraints
- MySQL 8.4 Reference Manual: CHECK Constraints
Related Articles
- Master MySQL Joins, Indexing, Query Optimization & ACID Transactions with Real-World Examples
- MySQL & SQL for Beginners: Understanding Databases, DBMS, and SQL from Scratch
- How to Design a Scalable E-Commerce & Multi-Vendor Marketplace Database
- Chapter 3: Variables, Data Types, and Memory: How JavaScript Stores Information

0 Comments