A table starts with a claim about what one row represents. In this lesson, one row is one customer. A name, email address or external reference is an attribute of that customer; choosing which attributes identify the entity is a separate decision. Write that sentence before writing CREATE TABLE. It prevents a convenient-looking column from becoming an accidental business rule.
A superkey identifies a row, possibly with redundant attributes. A candidate key is a minimal superkey: removing any part stops it identifying the row under the business rules. Choose one candidate as the primary key. Other candidate keys usually still need UNIQUE constraints. For example, an internal customer ID and a provider's external reference can each identify the customer. Keeping only the internal primary key would permit two rows for the same external identity.
A surrogate integer ID provides a stable reference when descriptions change. It does not discover whether two records represent the same person. That reconciliation needs an explicit business policy. Names are particularly poor identifiers: two customers may share one, and one customer may change theirs. Database keys should express allowed future states, rather than the coincidences in a small sample.
The following script retains two separate customers with the same name, then renames only one. Run it and connect each row to the identity diagram.
CREATE TABLE customers(
id INTEGER PRIMARY KEY,
external_ref TEXT NOT NULL UNIQUE,
display_name TEXT NOT NULL
);
INSERT INTO customers VALUES(7,'crm:a','Lee'),(20,'crm:b','Lee');
UPDATE customers SET display_name='L. Lee' WHERE id=7;
SELECT id,external_ref,display_name FROM customers ORDER BY id;Requiredness, uniqueness and acceptable values are independent. SQLite UNIQUE permits multiple NULLs, so a required business reference also needs NOT NULL. A nonempty reference needs a CHECK such as length(external_ref)>0; an empty string is not NULL. Case folding and whitespace normalization are additional policies. The build intentionally preserves case and whitespace, because silently normalizing identifiers can merge distinct entities.
SQLite's INTEGER PRIMARY KEY is a rowid alias with special behavior: inserting NULL can allocate an integer ID. Do not generalize “primary key means every explicitly supplied NULL errors” to this case. The build supplies explicit non-NULL IDs and checks that those identities survive import. For a textual primary key, use explicit NOT NULL rather than relying on SQLite's historical primary-key nullability behavior.
Work through the six checks by naming the exact constraint involved. In the bug hunts, distinguish a missing identity constraint from an overstrict descriptive field. The fill asks you to complete the business reference, and the build imports sparse IDs, equal names and malformed rows. Invalid imports must raise their constraint error; discarding conflicting rows would hide a broken source contract. After finishing, describe which future duplicate each key forbids and which equal values remain legitimate.