MySQL Data Types, Primary Keys and Foreign Keys Explained

MySQL Data Types, Primary Keys, Foreign Keys & Database Relationships

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

FamilyCommon typesTypical columns
IntegerTINYINT, SMALLINT, INT, BIGINTIDs, quantities, counts
Exact decimalDECIMAL(p,s)Prices, payments, tax, balances
ApproximateFLOAT, DOUBLEMeasurements, sensor readings, scores
TextCHAR, VARCHAR, TEXTNames, emails, phone numbers, descriptions
Date and timeDATE, TIME, DATETIME, TIMESTAMPBirth dates, delivery slots, order times
BooleanBOOLEAN (really TINYINT(1))is_active, is_paid, is_verified
SpecialENUM, JSONOrder 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:

TypeBytesSigned rangeUNSIGNED rangeIn words / typical use
TINYINT1-128 to 1270 to 255Age, ratings, small flags
SMALLINT2-32768 to 327670 to 65535Stock of one item in a small shop
MEDIUMINT3-8388608 to 83886070 to 16777215About 1.68 crore unsigned
INT4-2147483648 to 21474836470 to 4294967295About 214 crore signed, 429 crore unsigned
BIGINT8-9223372036854775808 to 92233720368547758070 to 18446744073709551615About 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
LengthFixed, padded to nVariable, up to n charactersVariable, up to 65,535 bytes
Trailing spacesRemoved on readKeptKept
IndexingFullFull (within index size limits)Prefix only
Good forPIN code, IFSC, PAN, country codeNames, emails, phones, titlesDescriptions, 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.

TypeStoresExampleRangeGood for
DATEDate only2026-11-031000-01-01 to 9999-12-31Birth date, invoice date, expiry date
TIMETime of day (or a duration)14:30:00-838:59:59 to 838:59:59Delivery slot, shop opening time
DATETIMEDate and time, as written2026-11-03 10:30:001000-01-01 00:00:00 to 9999-12-31 23:59:59Order time, scheduled dispatch, appointment
TIMESTAMPA moment in time, stored as UTC2026-11-03 10:30:001970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTCRow 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.

ColumnTypeWhy
customer_id, product_id, order_idINT UNSIGNED AUTO_INCREMENTWhole numbers, never negative, generated by MySQL
shop_name, product_nameVARCHAR(100)Short text of varying length
phoneVARCHAR(15)Not used in maths; keeps leading zeros and +91
pin_code / gstin / panCHAR(6) / CHAR(15) / CHAR(10)Fixed-length codes
rate, order totalsDECIMAL(10,2)Exact money
qtyINT UNSIGNEDWhole, non-negative count
descriptionTEXTLong, rarely filtered
is_activeBOOLEANYes/no flag
statusENUM(...)Short fixed list
ordered_at, created_atDATETIME DEFAULT CURRENT_TIMESTAMPBusiness time, no 2038 limit
attributesJSONOptional 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 GSTINNever
SizeOften long text (15 to 255 characters)4 or 8 bytes
Always available?Not always: some retailers have no GSTINYes, MySQL generates it
Exposes personal data?Yes, when it appears in URLs and other tablesNo

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.

Varun tries again: Varun rebuilds his tables with 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:

RuleWhat happens to child rowsGood 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
CASCADEChildren are deleted (or updated) along with the parentRows that mean nothing alone: order items of an order, a supplier's bank details
SET NULLThe child's foreign key becomes NULL; the child row staysOptional 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 keyForeign key
PurposeIdentifies each row in its own tablePoints to a row in another (or the same) table
UniquenessAlways uniqueRepeats freely (one customer, many orders)
NULL allowed?NeverYes, unless declared NOT NULL
How many per table?Exactly one (can span several columns)As many as you need
IndexIs the clustered index in InnoDBMySQL creates an index if none exists
Examplecustomers.customer_idorders.customer_id
Short versionIdentityRelationship

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.

customers PK customer_id shop_name phone (UNIQUE) orders PK order_id FK customer_id status order_items PK,FK order_id PK,FK product_id qty, rate products PK product_id product_name rate customer_kyc PK,FK customer_id gstin (UNIQUE) pan 1 N 1 N 1 N 1 1 customers 1:N orders (one shop, many orders) orders M:N products, through the junction table order_items customers 1:1 customer_kyc (optional, sensitive details) PK = primary key, FK = foreign key, N = many

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.

RelationshipExampleHow to build it
One-to-one (1:1)Customer and KYC recordForeign key that is also the primary key (or UNIQUE) in the child
One-to-many (1:N)Customer and ordersForeign key in the "many" table
Many-to-many (M:N)Orders and productsJunction 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, and NULL behaves oddly in comparisons and sums. Inserting NULL into a NOT NULL column fails with ERROR 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 with ERROR 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)

MistakeWhat goes wrongFix
FLOAT or DOUBLE for pricesTotals like 179500.4997253418; WHERE rate = 38.35 finds nothingDECIMAL(10,2), or whole paise in an integer column
INT or BIGINT for phone numbers, PIN codes, AadhaarOut-of-range errors, lost leading zeros, no room for +91VARCHAR(15) / CHAR(6) / CHAR(12), validated by format
VARCHAR for everything (prices, quantities, dates)Wrong sorting (1150 before 129), no validation, conversions in every queryA proper numeric or date type for each column
No foreign keys "for flexibility"Orphan orders and references to customers that never existedDeclare foreign keys from day one; find existing orphans with a LEFT JOIN ... IS NULL
CASCADE on everythingOne delete silently wipes orders and history across tablesRESTRICT 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 keyDuplicates, painful renames, slow lookups, a hidden row ID you cannot useINT 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

Related Articles

Post a Comment

0 Comments