Tables, Rows and Keys

You export a subscriber list to CSV, dedupe it in a spreadsheet, and import it back. The row count comes out higher than before. Nothing threw an error and no report looked wrong, which is the first sign that the table never had a rule about what a row means. It had a shape, which is not the same thing. Once you think in statements, keys and constraints stop being bookkeeping and become the definition of the table.

Most people meet tables through a spreadsheet, and the model transfers badly. In a spreadsheet a cell is a location: B7 holds a value until you type over it. In a relational database a row is a statement about the world, and the table is the set of statements currently believed to be true.

A relation is a set of tuples, so a row has no position

The object under the table is a relation: a set of tuples over a fixed set of named attributes. A tuple is an ordered list of values, and the attribute names say what each position holds. The word doing the work is set.

A set has no order and no duplicates, so two equal tuples are one element, and a query has no business promising you a position in the result. A bare SELECT in MySQL or SQLite returns rows in whatever order the storage engine finds convenient, and that order can change after an insert elsewhere. If you want an order, write ORDER BY, including when you only read the first few rows.

The test of whether you are thinking in relations or in cells: if two rows are identical in every column, can you still tell them apart? In a spreadsheet the answer is the row number. In a relation there is no second row.

SQL lets duplicates in, which is why the key is your job

Real tables are not quite relations. SQL permits duplicate rows and keeps them, so a table is a bag rather than a set, and this is the one place the textbook definition and the engine disagree. Look at what a table without a key accepts:

CREATE TABLE subscriber (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email        VARCHAR(255) NOT NULL,
  signed_up_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO subscriber (email) VALUES ('ana@example.com');
INSERT INTO subscriber (email) VALUES ('ana@example.com');

SELECT id, email, signed_up_at FROM subscriber;

+----+-----------------+---------------------+
| id | email           | signed_up_at        |
+----+-----------------+---------------------+
|  1 | ana@example.com | 2026-10-16 11:51:22 |
|  2 | ana@example.com | 2026-10-16 11:51:22 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

Both rows survived because id is generated. The surrogate key turns two equal statements into two distinct tuples, so the count of subscribers is two and the marketing export sends the newsletter twice. The mistake happened earlier, when the design never stated that an email identifies one subscriber.

A primary key is identity, not a performance trick

A primary key is the smallest set of columns whose values are never duplicated and never null. Once you declare one, the bag becomes a set again, and every row gets an address you can use in a foreign key.

CREATE TABLE app_user (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email        VARCHAR(255) NOT NULL,
  display_name VARCHAR(64) NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT uq_app_user_email UNIQUE (email)
);

INSERT INTO app_user (email, display_name) VALUES ('ana@example.com', 'Ana');
INSERT INTO app_user (email, display_name) VALUES ('ana@example.com', 'Ana');

ERROR 1062 (23000): Duplicate entry 'ana@example.com' for key 'app_user.uq_app_user_email'

Two promises are being made here. PRIMARY KEY (id) says every row is distinct and has one canonical handle. UNIQUE (email) says no two rows may describe the same email address. Engines enforce both with an index, and the primary key also forbids NULL.

One case leaves a hole. The SQL standard says NULL is not equal to NULL, so a unique index over a nullable column permits any number of NULL values. MySQL, PostgreSQL and SQLite all behave that way: convenient for an optional column, a trap when you meant "at most one row per value".

A natural key ties identity to a fact that can change

The obvious move with the email address is to make it the key. It is unique and you have it before the row exists. I have done it, and it works until the first customer changes their address.

Change one value and every reference to that customer changes with it. The invoice that pointed at the old address now appears to have always pointed at the new one, and the update has to reach every child table in the same transaction. If a second table stored the email for convenience, the update has two places to land and one of them gets missed.

The other half of the problem is trust. A natural key works when the value is unique and stable, and email addresses are neither. People share inboxes, businesses recycle addresses from lapsed domains, and a support tool can change an email without anyone deciding that the customer is a different customer.

A surrogate key replaces a fact you could not trust

A surrogate key has no meaning outside the database and is generated by the engine: AUTO_INCREMENT in MySQL, GENERATED ... AS IDENTITY in PostgreSQL, GENERATED ALWAYS AS IDENTITY in SQLite. It is assigned once and never edited, so it does not change when the world changes.

Two properties matter more than people expect. The key is narrow, so every foreign key pointing at it is narrow and those indexes stay small. A sequential integer also reveals insertion order through gaps in the sequence, which can be a problem when the number appears in a URL.

The property people get wrong is that a surrogate key proves nothing about the entity. It guarantees two rows are different, not that they describe different customers. A nightly import that fails to match on the natural key inserts a second row for a customer who already exists, the surrogate generates a fresh id, and the duplicate is permanent. You still need UNIQUE (email), on a value stable enough to be worth enforcing. Where no such value exists, the honest answer is a surrogate key plus a review step, not a constraint that pretends to a uniqueness the data cannot support.

The rule I use: surrogate key on every table other tables point at, unique constraint on every natural identifier you can verify. When the two disagree, the unique constraint is the one that saves you.

Foreign keys make every row outlive the code that wrote it

A referential integrity constraint says that a value in a child column must match an existing row in the parent column. The child column is the foreign key, the parent is usually a primary key, and the database checks on every write from every client. That last part is why it belongs in the schema and not in your service layer. A migration script, an admin console and a background job all have to satisfy it, and none of them can forget.

CREATE TABLE invoice (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  total_cents INT NOT NULL,
  CONSTRAINT fk_invoice_customer
    FOREIGN KEY (customer_id) REFERENCES app_user (id)
    ON DELETE RESTRICT
);

INSERT INTO invoice (customer_id, total_cents) VALUES (999, 4500);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`wpsifero_lab`.`invoice`, CONSTRAINT `fk_invoice_customer` FOREIGN KEY
(`customer_id`) REFERENCES `app_user` (`id`) ON DELETE RESTRICT)

The ON DELETE clause answers the harder question: what should happen to the invoices when the customer disappears. RESTRICT refuses the delete, which is what you want for financial records. CASCADE deletes the children too, which suits a draft that means nothing without its parent. SET NULL keeps the child and blanks the reference, which is sensible for optional links and dangerous when the link carried meaning:

CREATE TABLE note (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  person_id INT NULL,
  body      VARCHAR(64) NOT NULL,
  CONSTRAINT fk_note_person FOREIGN KEY (person_id) REFERENCES person (id)
    ON DELETE SET NULL
);

INSERT INTO person (email) VALUES ('ana@example.com');
INSERT INTO note (person_id, body) VALUES (1, 'called back'), (NULL, 'imported from CSV');
DELETE FROM person WHERE id = 1;
SELECT * FROM note;

+----+-----------+-------------------+
| id | person_id | body              |
+----+-----------+-------------------+
|  1 |      NULL | called back       |
|  2 |      NULL | imported from CSV |
+----+-----------+-------------------+
2 rows in set (0.00 sec)

Two rows that meant different things now look identical, so the distinction is gone. If it matters, SET NULL was the wrong choice. Either the column stays NOT NULL with RESTRICT, or it is nullable and documented as "author unknown". That is a decision to make on purpose, not a default to accept because the engine allowed it.

A key and an index are different things that share a structure

A key is a promise about the data. An index is a structure that makes a lookup cheap. The engine satisfies a primary key or unique constraint by building a B-tree index, so for those two the promise and the structure arrive together. People conclude that a key is an index with extra paperwork, which produces two mistakes in opposite directions.

The first is believing that any index gives you uniqueness. A plain index on email makes WHERE email = ? fast and leaves the duplicate rows exactly where they were. The second is believing that a constraint is required to get an index. A foreign key column is usually worth indexing, and no engine creates that index for you: MySQL, PostgreSQL and SQLite all expect you to index the child column yourself if you want the parent delete check and the join to be fast. You can see which structures exist with a command per engine.

SHOW INDEX FROM app_user;                      -- MySQL and MariaDB
SELECT indexname, indexdef FROM pg_indexes
  WHERE tablename = 'app_user';                -- PostgreSQL
PRAGMA index_list('app_user');                 -- SQLite

+----------+------------+-------------------+--------------+-------------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name |
+----------+------------+-------------------+--------------+-------------+
| app_user |          0 | PRIMARY           |            1 | id          |
| app_user |          0 | uq_app_user_email |            1 | email       |
+----------+------------+-------------------+--------------+-------------+

Non_unique = 0 on both rows is the promise showing through: these are unique indexes, and the constraint is what asked for them. A plain index would show Non_unique = 1 with no constraint behind it. Keys answer "which row is this". Indexes answer "how do I find it quickly", and the answer changes with the queries your application actually runs.

The subscription table is where you decide all of this at once

CREATE TABLE subscription (
  customer_email VARCHAR(255) NOT NULL PRIMARY KEY,
  plan_code      VARCHAR(16)  NOT NULL,
  started_on     DATE         NOT NULL,
  cancelled_on   DATE         NULL
);

CREATE TABLE customer (
  id    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT uq_customer_email UNIQUE (email)
);

CREATE TABLE subscription (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id  BIGINT UNSIGNED NOT NULL,
  plan_code    VARCHAR(16) NOT NULL,
  started_on   DATE NOT NULL,
  cancelled_on DATE NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_subscription_customer
    FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE RESTRICT,
  CONSTRAINT ck_subscription_dates
    CHECK (cancelled_on IS NULL OR cancelled_on >= started_on)
);