Chapter 3 · Creating and Changing Data
CREATE, ALTER, DROP and Constraints
- Page 11 of 22
- 17 min read
So far you have read tables that already existed. Now you build your own. A table is more than a list of columns: it is also a set of rules the database enforces on every row, forever. Those rules are called constraints, and they are the cheapest data-quality check you will ever write: a review with a rating of 9, a label spelled "postive", or an order for a customer who does not exist is refused at the door instead of quietly poisoning a report or a training set months later.
On this page you create a reviews table for BitByte Shop (the kind of table that later becomes sentiment-analysis training data), break every rule on purpose, change the table with ALTER TABLE, and learn the difference between emptying a table and destroying it.
What you will learn
- How to write
CREATE TABLEwithPRIMARY KEY,FOREIGN KEY,UNIQUE,NOT NULL,CHECKandDEFAULT. - What each constraint violation looks like in SQLite, MySQL and PostgreSQL.
- Auto-numbered ids:
INTEGER PRIMARY KEY/AUTOINCREMENT,AUTO_INCREMENT,IDENTITYandSERIAL. - What
ALTER TABLEcan and cannot do, especially in SQLite. DROPvsTRUNCATEvsDELETE, and why foreign keys can stop all three.
CREATE TABLE with constraints
Every column gets a name and a type (see Data Types, NULL and Choosing the Right Type), followed by its constraints. Rules that involve several columns go at the end, as table constraints; so do the foreign keys, for a reason you will see below (MySQL ignores a REFERENCES written next to the column):
CREATE TABLE IF NOT EXISTS reviews (
review_id INTEGER PRIMARY KEY AUTOINCREMENT, -- unique id, filled in for you
product_id INTEGER NOT NULL, -- must be a real product (FOREIGN KEY below)
customer_id INTEGER, -- NULL = anonymous review
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
body TEXT NOT NULL,
label TEXT CONSTRAINT valid_label
CHECK (label IN ('positive', 'neutral', 'negative')),
status TEXT NOT NULL DEFAULT 'new',
UNIQUE (product_id, customer_id), -- one review per customer per product
FOREIGN KEY (product_id) REFERENCES products (product_id),
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
INSERT INTO reviews (product_id, customer_id, rating, body, label)
VALUES (3, 2, 5, 'Keys feel great, a bit loud.', 'positive'),
(4, 7, 2, 'Stopped working after a week.', 'negative');
SELECT * FROM reviews;+-----------+------------+-------------+--------+-------------------------------+----------+--------+
| review_id | product_id | customer_id | rating | body | label | status |
+-----------+------------+-------------+--------+-------------------------------+----------+--------+
| 1 | 3 | 2 | 5 | Keys feel great, a bit loud. | positive | new |
| 2 | 4 | 7 | 2 | Stopped working after a week. | negative | new |
+-----------+------------+-------------+--------+-------------------------------+----------+--------+Notice what you did not type: no review_id (the database numbered the rows) and no status (the DEFAULT filled in 'new'). IF NOT EXISTS makes the statement safe to run twice: the second time it does nothing instead of failing with table reviews already exists. It does not update an existing table to match your new definition.
| Constraint | Guarantees | Typical use in data/AI work |
|---|---|---|
PRIMARY KEY | Every row has a unique, non-empty identifier | An id you can join on, deduplicate on and trace back to |
FOREIGN KEY / REFERENCES | The value exists in another table | Every prediction belongs to a real model run |
UNIQUE | No two rows share the value (or combination) | One evaluation score per (model, test question) |
NOT NULL | The column always has a value | No training example without its text |
CHECK | A condition holds for every row | Labels from a fixed set, probabilities between 0 and 1 |
DEFAULT | A value used when the insert leaves the column out | Status 'new', a counter starting at 0, a creation time |
Breaking the rules on purpose
A constraint you have never seen fail is a constraint you do not really understand. Each of these inserts breaks one rule; SQLite refuses the row and tells you which rule:
INSERT INTO reviews (product_id, customer_id, rating) VALUES (4, 2, 4);Error: NOT NULL constraint failed: reviews.bodyINSERT INTO reviews (product_id, customer_id, rating, body) VALUES (4, 2, 9, 'Amazing!');Error: CHECK constraint failed: rating BETWEEN 1 AND 5INSERT INTO reviews (product_id, customer_id, rating, body, label)
VALUES (4, 2, 4, 'Good value.', 'postive');Error: CHECK constraint failed: valid_labelINSERT INTO reviews (product_id, customer_id, rating, body) VALUES (3, 2, 4, 'Changed my mind.');Error: UNIQUE constraint failed: reviews.product_id, reviews.customer_idINSERT INTO reviews (product_id, customer_id, rating, body) VALUES (99, 2, 4, 'Which product?');Error: FOREIGN KEY constraint failedINSERT INTO reviews (review_id, product_id, customer_id, rating, body) VALUES (1, 5, 2, 5, 'Sharp.');Error: UNIQUE constraint failed: reviews.review_idWhat to notice:
- The
CHECKwe named (CONSTRAINT valid_label) reports its name; unnamed ones report their expression (SQLite) or an invented name (other databases, below). Name the rules you care about. - SQLite's foreign-key message does not say which key failed. Run
PRAGMA foreign_key_check;to list rows that break foreign keys in a table that already has data. - A failed statement changes nothing. The table still has exactly the two rows from before.
NULL slips through CHECK and UNIQUE
A CHECK rejects a row only when its condition is false. With a NULL the condition is unknown, and unknown passes. That is why label may be NULL (an unlabelled review) and why the shop's stock column, CHECK (stock >= 0), still allows NULL for courses. Likewise, UNIQUE treats NULLs as all different (in SQLite, MySQL and PostgreSQL; SQL Server allows only one NULL), so any number of anonymous reviews of the same product are allowed:
INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (5, NULL, 4, 'Great colours.');
INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (5, NULL, 3, 'Too big for my desk.');
SELECT review_id, product_id, customer_id, rating, label FROM reviews ORDER BY review_id;+-----------+------------+-------------+--------+----------+
| review_id | product_id | customer_id | rating | label |
+-----------+------------+-------------+--------+----------+
| 1 | 3 | 2 | 5 | positive |
| 2 | 4 | 7 | 2 | negative |
| 3 | 5 | NULL | 4 | NULL |
| 4 | 5 | NULL | 3 | NULL |
+-----------+------------+-------------+--------+----------+If a value is required, say so with NOT NULL; CHECK alone is not enough.
Foreign keys: on, off, and the MySQL trap
SQLite only checks foreign keys when the connection asks for it with PRAGMA foreign_keys = ON. sqlhelp.py does this for you, and DB Browser for SQLite does it by default, but a plain sqlite3.connect() and the sqlite3 shell start with it off:
import sqlite3
raw = sqlite3.connect("shop.db") # a plain connection: no PRAGMA
print("foreign_keys =", raw.execute("PRAGMA foreign_keys").fetchone()[0])
raw.execute("INSERT INTO reviews (product_id, rating, body) VALUES (999, 5, 'Orphan review')")
print("orphans:", raw.execute("SELECT COUNT(*) FROM reviews WHERE product_id = 999").fetchone()[0])
raw.rollback() # undo it, then close
raw.close()foreign_keys = 0
orphans: 1The review for product 999 went in without a murmur. Every program that writes to SQLite should run the PRAGMA right after connecting.
MySQL has its own trap. MySQL 8.0 parses and silently ignores a REFERENCES written next to a column (support arrived only in MySQL 9.0). Here is a throwaway table written in that inline style:
-- MySQL 8.0: an inline REFERENCES is accepted, then ignored
CREATE TABLE wishlist (
wish_id INT PRIMARY KEY,
product_id INT NOT NULL REFERENCES products (product_id) -- no foreign key is created
);
INSERT INTO wishlist (wish_id, product_id) VALUES (1, 999);
SELECT * FROM wishlist;+---------+------------+
| wish_id | product_id |
+---------+------------+
| 1 | 999 |
+---------+------------+Product 999 does not exist, yet the row was stored, with no error and no warning. This is exactly why shop.sql declares every foreign key as a table constraint at the end of each CREATE TABLE. In MySQL always write FOREIGN KEY (col) REFERENCES other (col); it works everywhere.
Auto-numbered ids in each database
| Database | How to write it | Notes |
|---|---|---|
| SQLite | id INTEGER PRIMARY KEY | Next id is max + 1, so the id of a deleted last row can be reused |
| SQLite | id INTEGER PRIMARY KEY AUTOINCREMENT | Never reuses an id (remembered in sqlite_sequence); slightly slower |
| MySQL | id INT AUTO_INCREMENT PRIMARY KEY | One per table |
| PostgreSQL | id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY | The SQL standard; SERIAL is the older shorthand you will still see |
| SQL Server | id INT IDENTITY(1,1) PRIMARY KEY | Start at 1, step 1 |
Here is the same table on the two servers. Like the SQLite version, the MySQL one writes the foreign key as a table constraint, so that it really exists:
-- MySQL
CREATE TABLE reviews (
review_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
customer_id INT,
rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
body TEXT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'new',
UNIQUE (product_id, customer_id),
FOREIGN KEY (product_id) REFERENCES products (product_id)
);
INSERT INTO reviews (product_id, customer_id, rating, body)
VALUES (3, 2, 5, 'Great keys'), (4, 7, 2, 'Broke fast');
SELECT review_id, product_id, rating, status FROM reviews ORDER BY review_id;
INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (4, 2, 9, 'Amazing!');+-----------+------------+--------+--------+
| review_id | product_id | rating | status |
+-----------+------------+--------+--------+
| 1 | 3 | 5 | new |
| 2 | 4 | 2 | new |
+-----------+------------+--------+--------+
Error: ERROR 3819: Check constraint 'reviews_chk_1' is violated.-- PostgreSQL
CREATE TABLE reviews (
review_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id INTEGER NOT NULL,
customer_id INTEGER,
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
body TEXT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'new',
UNIQUE (product_id, customer_id),
FOREIGN KEY (product_id) REFERENCES products (product_id),
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
INSERT INTO reviews (product_id, customer_id, rating, body)
VALUES (3, 2, 5, 'Great keys'), (4, 7, 2, 'Broke fast');
SELECT review_id, product_id, rating, status FROM reviews ORDER BY review_id;
INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (4, 2, 9, 'Amazing!');+-----------+------------+--------+--------+
| review_id | product_id | rating | status |
+-----------+------------+--------+--------+
| 1 | 3 | 5 | new |
| 2 | 4 | 2 | new |
+-----------+------------+--------+--------+
Error: new row for relation "reviews" violates check constraint "reviews_rating_check"Same rule, three different messages; the unnamed constraint got the invented names reviews_chk_1 and reviews_rating_check. GENERATED ALWAYS also refuses a hand-typed id, which protects the numbering:
-- PostgreSQL
INSERT INTO reviews (review_id, product_id, customer_id, rating, body) VALUES (7, 5, 2, 4, 'Sharp.');Error: cannot insert a non-DEFAULT value into column "review_id"ALTER TABLE: changing a table that has data
Requirements change: the product team wants a "helpful" vote counter, and body should really be called review_text. SQLite supports adding, renaming and dropping columns, and renaming the table:
ALTER TABLE reviews ADD COLUMN helpful_votes INTEGER NOT NULL DEFAULT 0;
ALTER TABLE reviews RENAME COLUMN body TO review_text; -- SQLite 3.25+
ALTER TABLE reviews DROP COLUMN status; -- SQLite 3.35+
SELECT review_id, rating, review_text, helpful_votes FROM reviews ORDER BY review_id;+-----------+--------+-------------------------------+---------------+
| review_id | rating | review_text | helpful_votes |
+-----------+--------+-------------------------------+---------------+
| 1 | 5 | Keys feel great, a bit loud. | 0 |
| 2 | 2 | Stopped working after a week. | 0 |
| 3 | 4 | Great colours. | 0 |
| 4 | 3 | Too big for my desk. | 0 |
+-----------+--------+-------------------------------+---------------+The existing rows got the default 0. Without a default, a NOT NULL column cannot be added, because the old rows would have nothing to put in it:
ALTER TABLE reviews ADD COLUMN verified INTEGER NOT NULL;Error: Cannot add a NOT NULL column with default value NULLSQLite's ALTER TABLE stops there. It cannot change a column's type, add or remove a constraint on an existing column, or drop a column that is part of a key, a UNIQUE rule or an index:
ALTER TABLE reviews ALTER COLUMN review_text TYPE VARCHAR(500);Error: near "ALTER": syntax errorFor those changes SQLite uses a rebuild: create a new table with the definition you want, copy the rows across with INSERT … SELECT, drop the old table and rename the new one, all inside one transaction. The servers can change columns in place:
-- PostgreSQL
ALTER TABLE reviews ALTER COLUMN body TYPE VARCHAR(500);
ALTER TABLE reviews ADD COLUMN helpful_votes INTEGER NOT NULL DEFAULT 0;
ALTER TABLE reviews ADD CONSTRAINT votes_not_negative CHECK (helpful_votes >= 0);
UPDATE reviews SET helpful_votes = -1 WHERE review_id = 1;Error: new row for relation "reviews" violates check constraint "votes_not_negative"-- MySQL
ALTER TABLE reviews MODIFY COLUMN body VARCHAR(500) NOT NULL; -- MySQL restates the whole column
ALTER TABLE reviews RENAME COLUMN status TO review_status;
ALTER TABLE reviews ADD COLUMN helpful_votes INT NOT NULL DEFAULT 0;
SELECT review_id, body, review_status, helpful_votes FROM reviews ORDER BY review_id;+-----------+------------+---------------+---------------+
| review_id | body | review_status | helpful_votes |
+-----------+------------+---------------+---------------+
| 1 | Great keys | new | 0 |
| 2 | Broke fast | new | 0 |
+-----------+------------+---------------+---------------+Adding a constraint to a table that already has data checks the old rows too; if one breaks the rule, the ALTER fails and you must clean the data first. On a big production table, schema changes need care (the advanced tutorial covers migrations).
DROP vs TRUNCATE vs DELETE
| Statement | What it removes | Table still exists? | Notes |
|---|---|---|---|
DELETE FROM t WHERE … | The matching rows | Yes | Row by row, can be rolled back inside a transaction; without WHERE it removes every row |
TRUNCATE TABLE t | Every row, fast | Yes | MySQL, PostgreSQL, SQL Server. SQLite has no TRUNCATE: use DELETE FROM t; (SQLite optimises it). In MySQL, TRUNCATE commits at once and cannot be rolled back |
DROP TABLE t | The rows and the table definition | No | Add IF EXISTS in scripts that may run twice |
Foreign keys guard all three. You cannot remove a table that other rows still point to:
DROP TABLE customers;Error: FOREIGN KEY constraint failed-- PostgreSQL
TRUNCATE TABLE customers;Error: cannot truncate a table referenced in a foreign key constraintA quick backup copy is tempting: CREATE TABLE … AS SELECT copies the rows, but not the constraints. Compare the column definitions SQLite stored for the original and the copy (pragma_table_info lists a table's columns):
CREATE TABLE products_copy AS SELECT * FROM products;
SELECT name, type, "notnull", pk FROM pragma_table_info('products') ORDER BY cid;
SELECT name, type, "notnull", pk FROM pragma_table_info('products_copy') ORDER BY cid;+-------------+--------------+---------+----+
| name | type | notnull | pk |
+-------------+--------------+---------+----+
| product_id | INTEGER | 0 | 1 |
| name | VARCHAR(100) | 1 | 0 |
| category_id | INTEGER | 0 | 0 |
| price | INTEGER | 1 | 0 |
| stock | INTEGER | 0 | 0 |
+-------------+--------------+---------+----+
+-------------+------+---------+----+
| name | type | notnull | pk |
+-------------+------+---------+----+
| product_id | INT | 0 | 0 |
| name | TEXT | 0 | 0 |
| category_id | INT | 0 | 0 |
| price | INT | 0 | 0 |
| stock | INT | 0 | 0 |
+-------------+------+---------+----+No primary key, no NOT NULL, no CHECK (price > 0). Fine for a throw-away snapshot, wrong as a real table. Now tidy up. Drop your own tables when you finish a page: sqlhelp.reset() rebuilds only the shop's tables, so a leftover reviews would stay behind, still pointing at products that were dropped and re-created under it:
DELETE FROM products_copy; -- SQLite's "truncate"
SELECT COUNT(*) AS rows_left FROM products_copy;
DROP TABLE products_copy;
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS reviews; -- second time: IF EXISTS makes it a no-op+-----------+
| rows_left |
+-----------+
| 0 |
+-----------+Where this shows up in real systems
- Labelling tools store annotations in tables with
CHECK (label IN (…)), so a typo in a label cannot reach the training set. - Evaluation logs use
UNIQUE (model_name, question_id), so re-running an eval script cannot double-count scores. - Data pipelines load into constrained tables on purpose: a bad batch fails loudly at load time instead of producing a wrong dashboard.
Common mistakes
- Foreign keys that are not enforced. SQLite without the PRAGMA, or a MySQL 8.0 table with inline
REFERENCES, accepts orphan rows silently. Fix:PRAGMA foreign_keys = ONon every SQLite connection, and table-levelFOREIGN KEY (…) REFERENCES …in MySQL. - Trusting CHECK or UNIQUE to stop NULLs. Both let NULL through. Fix: add
NOT NULLwhen the value is required, e.g.rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5). - Copying a table with
CREATE TABLE … AS SELECTand treating it as the real thing. The copy has no keys or rules. Fix: write the fullCREATE TABLE, thenINSERT INTO new_table SELECT … FROM old_table;. - Unnamed constraints.
reviews_chk_1 is violatedtells a teammate nothing. Fix:CONSTRAINT rating_1_to_5 CHECK (rating BETWEEN 1 AND 5). - Adding a required column to a table that has rows. It fails (or, on a server, needs a value for every old row). Fix: give it a
DEFAULT, or add it as nullable, fill it withUPDATE, then add the rule.
Try it yourself
- Easy: Create a table
newsletterwith an auto-numberedsubscriber_id, anemailthat is required and unique, and alanguagethat defaults to'bn'. Insert one subscriber without a language and select the row. - Medium: Create a table
model_runsfor ML experiments:run_id,model_name(required),accuracy(aREALbetween 0 and 1, required) andstatusthat must be'running','finished'or'failed', default'running'. Insert one valid run, then try an accuracy of 1.7 and read the error. - Hard: SQLite cannot add a
CHECKto an existing column. Add the rule "languagemust be'bn'or'en'" tonewsletterby rebuilding the table inside a transaction, keeping its rows.
Answers
-- Easy
CREATE TABLE newsletter (
subscriber_id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
language TEXT NOT NULL DEFAULT 'bn'
);
INSERT INTO newsletter (email) VALUES ('rafiq@example.com');
SELECT * FROM newsletter;+---------------+-------------------+----------+
| subscriber_id | email | language |
+---------------+-------------------+----------+
| 1 | rafiq@example.com | bn |
+---------------+-------------------+----------+-- Medium
CREATE TABLE model_runs (
run_id INTEGER PRIMARY KEY,
model_name TEXT NOT NULL,
accuracy REAL NOT NULL CONSTRAINT accuracy_0_to_1 CHECK (accuracy BETWEEN 0 AND 1),
status TEXT NOT NULL DEFAULT 'running'
CHECK (status IN ('running', 'finished', 'failed'))
);
INSERT INTO model_runs (model_name, accuracy) VALUES ('logreg-v1', 0.87);
INSERT INTO model_runs (model_name, accuracy) VALUES ('logreg-v2', 1.7);Error: CHECK constraint failed: accuracy_0_to_1-- Hard: the SQLite rebuild recipe
BEGIN;
CREATE TABLE newsletter_new (
subscriber_id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
language TEXT NOT NULL DEFAULT 'bn' CHECK (language IN ('bn', 'en'))
);
INSERT INTO newsletter_new (subscriber_id, email, language)
SELECT subscriber_id, email, language FROM newsletter;
DROP TABLE newsletter;
ALTER TABLE newsletter_new RENAME TO newsletter;
COMMIT;
SELECT * FROM newsletter;
INSERT INTO newsletter (email, language) VALUES ('mitu@example.com', 'fr');+---------------+-------------------+----------+
| subscriber_id | email | language |
+---------------+-------------------+----------+
| 1 | rafiq@example.com | bn |
+---------------+-------------------+----------+
Error: CHECK constraint failed: language IN ('bn', 'en')Summary
- Constraints are rules the database enforces on every row:
PRIMARY KEY,FOREIGN KEY,UNIQUE,NOT NULL,CHECK, plusDEFAULTfor missing values. CHECKandUNIQUElet NULL through; addNOT NULLwhen a value is required. Name important constraints.- Foreign keys need
PRAGMA foreign_keys = ONin SQLite and table-level syntax in MySQL 8.0. - Auto ids:
INTEGER PRIMARY KEY(SQLite),AUTO_INCREMENT(MySQL),GENERATED … AS IDENTITYorSERIAL(PostgreSQL),IDENTITY(SQL Server). - SQLite's
ALTER TABLEcan add, rename and drop columns; anything else is a rebuild.DELETEremoves rows,TRUNCATEempties fast (not in SQLite),DROPremoves the table.
Next: INSERT, UPDATE, DELETE and Upserts, Safely. With tables and rules in place, you will add, change and remove rows, upsert data that may already exist, and follow a routine that keeps an UPDATE without a WHERE from ruining your day.