Practice: keys and constraints
50 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Constraints are rules the database checks on every row, whichever application or person sends it: PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, CHECK and FOREIGN KEY. Practise choosing them from a requirement, predicting what they refuse, and adding them to tables that already hold bad data.
Adding the right constraints to a table
A constraint checklist is a fixed set of questions asked about every column: is it the key, required, defaulted, unique, limited to a range, or a link to another table. Going column by column means no rule is forgotten.
Example 1 builds a UPI transfer table by asking the same questions about every column.
Example 1 · From a requirement to a protected table
-- Requirement: a UPI app records transfers between accounts. Each transfer has-- a sender and a receiver (both real accounts, never the same one), an amount-- from Rs. 1 to Rs. 1,00,000, a unique reference number, and a status that-- starts as 'PENDING'.CREATE TABLE upi_accounts ( account_id INT AUTO_INCREMENT PRIMARY KEY, vpa VARCHAR(50) NOT NULL, CONSTRAINT uq_upi_accounts_vpa UNIQUE (vpa));
CREATE TABLE upi_transfers ( transfer_id INT AUTO_INCREMENT PRIMARY KEY, ref_no CHAR(12) NOT NULL, from_acc INT NOT NULL, to_acc INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL DEFAULT 'PENDING', CONSTRAINT uq_upi_transfers_ref UNIQUE (ref_no), CONSTRAINT chk_upi_transfers_amount CHECK (amount BETWEEN 1 AND 100000), CONSTRAINT chk_upi_transfers_self CHECK (from_acc <> to_acc), CONSTRAINT fk_upi_transfers_from FOREIGN KEY (from_acc) REFERENCES upi_accounts (account_id) ON DELETE RESTRICT, CONSTRAINT fk_upi_transfers_to FOREIGN KEY (to_acc) REFERENCES upi_accounts (account_id) ON DELETE RESTRICT);
DESCRIBE upi_transfers;+-------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+----------------+ | transfer_id | int | NO | PRI | NULL | auto_increment | | ref_no | char(12) | NO | UNI | NULL | | | from_acc | int | NO | MUL | NULL | | | to_acc | int | NO | MUL | NULL | | | amount | decimal(10,2) | NO | | NULL | | | status | varchar(10) | NO | | PENDING | | +-------------+---------------+------+-----+---------+----------------+
What this code does
- Every sentence of the requirement became a rule: real accounts → two foreign keys, never the same →
from_acc <> to_acc, the amount range → a CHECK, unique reference → UNIQUE, starts as PENDING → DEFAULT. - Two foreign keys may point at the same parent table. RESTRICT keeps money records even if someone tries to delete an account.
DESCRIBEshowsUNIonref_noandMULon the two foreign key columns; CHECK rules do not appear there.
Ask these questions about every column:
- Which column names the row? → PRIMARY KEY (usually a surrogate
INT AUTO_INCREMENT). - Must it always have a value? → NOT NULL; is there an obvious value? → DEFAULT.
- Must it be different in every row, alone or with other columns? → UNIQUE.
- Does it have a valid range or list, or a rule with another column? → CHECK. Does it point to another table? → FOREIGN KEY with an ON DELETE rule.
Give every constraint a name with the table in it, so the errors are readable.
Predicting which inserts fail
Example 2 traces a batch of inserts against the ShopLite products table, one rule at a time.
Example 2 · Tracing inserts against ShopLite products
-- products: product_id AUTO_INCREMENT PRIMARY KEY, name NOT NULL,-- category_id NULL with a foreign key, price NOT NULL CHECK (price > 0),-- stock NOT NULL DEFAULT 0
-- 1. Category 4 exists, price is positive, stock takes its defaultINSERT INTO products (name, category_id, price) VALUES ('USB-C Cable', 4, 249.00);
-- 2. NULL category is allowed: the foreign key does not check NULLINSERT INTO products (name, category_id, price, stock) VALUES ('Gift Card Rs. 1000', NULL, 1000.00, 500);
SELECT * FROM products WHERE product_id > 15 ORDER BY product_id;
-- 3. A price of zero breaks the CHECKINSERT INTO products (name, category_id, price) VALUES ('Free Sticker', 4, 0);+------------+--------------------+-------------+---------+-------+ | product_id | name | category_id | price | stock | +------------+--------------------+-------------+---------+-------+ | 16 | USB-C Cable | 4 | 249.00 | 0 | | 17 | Gift Card Rs. 1000 | NULL | 1000.00 | 500 | +------------+--------------------+-------------+---------+-------+ ERROR 3819 (HY000): Check constraint 'products_chk_1' is violated.
What this code does
- Rows 1 and 2 pass every rule and get ids 16 and 17. The cable's
stockis 0 from the DEFAULT; the gift card's NULL category passes the foreign key. - Row 3 fails
products_chk_1, the unnamedprice > 0rule from the practice script, and the client stops there.
To predict whether an insert works, test the row against each rule of the table, one by one: is the key new, are all NOT NULL columns given (or defaulted), are UNIQUE values new, do all CHECKs hold, and does every foreign key value exist in its parent?
Remember the special cases. A column left out takes its DEFAULT; an explicit NULL does not. NULL passes UNIQUE, CHECK and FOREIGN KEY, and is stopped only by NOT NULL. Text comparisons ignore case with the default collation. In a script, the mysql client stops at the first failing statement, and a failing multi-row INSERT stores none of its rows.
Predict the output
Which inserts are accepted?
products: name NOT NULL, category_id may be NULL, price CHECK (price > 0), stock DEFAULT 0.
Round 1 of 2
Fixing tables that accept bad data
Tightening a table means adding constraints to a table that already holds rows. MySQL checks the existing rows, so any bad row makes the ALTER fail until it is fixed.
Example 3 finds wallet rows with a zero or negative amount, fixes them, and only then adds the rule.
Example 3 · Find, fix, then add the rule
CREATE TABLE wallet_topups ( topup_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, amount INT NOT NULL);INSERT INTO wallet_topups (user_id, amount) VALUES (1, 500), (2, 0), (1, -200), (3, 1000);
-- Step 1: find the rows that break the ruleSELECT * FROM wallet_topups WHERE amount <= 0 ORDER BY topup_id;
-- Step 2: fix them (here the team agreed they were test rows worth Rs. 1)UPDATE wallet_topups SET amount = 1 WHERE amount <= 0;
-- Step 3: add the rule; it passes nowALTER TABLE wallet_topups ADD CONSTRAINT chk_wallet_topups_amount CHECK (amount > 0);
SELECT * FROM wallet_topups ORDER BY topup_id;+----------+---------+--------+ | topup_id | user_id | amount | +----------+---------+--------+ | 2 | 2 | 0 | | 3 | 1 | -200 | +----------+---------+--------+ +----------+---------+--------+ | topup_id | user_id | amount | +----------+---------+--------+ | 1 | 1 | 500 | | 2 | 2 | 1 | | 3 | 1 | 1 | | 4 | 3 | 1000 | +----------+---------+--------+
What this code does
- Adding the CHECK first would fail with error 3819, because rows 2 and 3 break it. The SELECT finds them before you change anything.
- After the UPDATE every row passes, so the ALTER succeeds and future top-ups of 0 or less are refused.
- What the "right" value is (fix or delete) is a business decision; the database only enforces the rule you choose.
A bad row makes the ALTER fail with error 1062 for UNIQUE, 3819 for CHECK, 1138 for NOT NULL and 1452 for a foreign key.
So work in three steps. First find the bad rows with a SELECT. Then fix them with UPDATE (or remove true junk with DELETE), after agreeing what the right value is. Finally add the constraint, which now passes and protects every future row.
Reading any constraint error
SQLSTATE 23000 is the standard code, shown in brackets in the error, for a broken data rule. Every database uses it, so applications can catch it and show a friendly message.
Example 4 lists every rule of two ShopLite tables, so you can match each error to the rule that raised it.
Example 4 · Every rule of two ShopLite tables
SELECT table_name, constraint_name, constraint_typeFROM information_schema.table_constraintsWHERE table_schema = DATABASE() AND table_name IN ('products', 'order_items')ORDER BY table_name, constraint_type, constraint_name;+-------------+--------------------+-----------------+ | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | +-------------+--------------------+-----------------+ | order_items | order_items_chk_1 | CHECK | | order_items | order_items_ibfk_1 | FOREIGN KEY | | order_items | order_items_ibfk_2 | FOREIGN KEY | | order_items | PRIMARY | PRIMARY KEY | | products | products_chk_1 | CHECK | | products | products_ibfk_1 | FOREIGN KEY | | products | PRIMARY | PRIMARY KEY | +-------------+--------------------+-----------------+
What this code does
IN ('products', 'order_items')means "either of these two tables"; the Querying module covers IN.- Matching names to errors:
order_items_chk_1isquantity > 0,products_chk_1isprice > 0, and theibfkrows are the foreign keys from the previous lessons. - NOT NULL and DEFAULT are column settings, not named constraints, so they do not appear here;
DESCRIBEshows them.
Every constraint error names the rule that failed, and usually the value too. The numbers you will meet most often:
1062Duplicate entry: a PRIMARY KEY or UNIQUE value is already used.1048cannot be null /1364doesn't have a default value: a NOT NULL column got NULL or nothing.3819Check constraint is violated: a CHECK rule is false for the row.1452cannot add a child row /1451cannot delete a parent row: a foreign key link would break.
Applications usually catch 23000 and show a friendly message such as "this email is already registered".
Next step
Check your understanding
12 quick questions