{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"keys-and-identity","title":"Keys: how a database knows one thing from another","url":"https://addaly.com/learn/data-and-sql/keys-and-identity","position":4,"of":66,"minutes":8,"answer":"A primary key answers \"is this the same thing as before?\", so it must be meaningless and permanent; a business rule such as a unique email sits beside it and is allowed to change.","keyIdea":"A primary key answers \"is this the same thing as before?\", so it must be meaningless and permanent; a business rule such as a unique email sits beside it and is allowed to change."},"body":{"format":"markdown","text":"## The question a key answers\n\nTwo rows say `Ade Okafor`. Are they one person who ordered twice, or two people who share a common name? Nothing about the text can tell you. A key is the column, or set of columns, that settles it: the thing the database and the rest of your system agree means *this one, and no other*.\n\n## Natural keys and their disappointments\n\nA natural key is a value that already exists in the world and looks unique: an email address, a phone number, a national ID, a vehicle registration.\n\nEach one fails in a way you will meet:\n\n- **Email.** People share them (family accounts), change them, and reuse them: an abandoned address gets reassigned by the provider a year later.\n- **Phone number.** Recycled aggressively by telecoms. A number that was one customer's is another's within months.\n- **National ID.** Not everybody has one, transcription errors are common, and storing it makes your database a target and a legal responsibility.\n- **A product's SKU.** Reissued by suppliers. Changed at rebrand.\n\nThe deeper problem is not uniqueness, it is *stability*. If the key changes, every row that referenced it must change too, and any exported report becomes unjoinable to the present.\n\n## Surrogate keys\n\nSo most tables use a surrogate key: a meaningless number the database generates and never changes.\n\n```sql\nCREATE TABLE customers (\n  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  email  text NOT NULL UNIQUE,\n  phone  text\n);\n```\n\nBoth properties are present and they are different. `PRIMARY KEY` on `id` is the internal identity: permanent, meaningless, what other tables point at. `UNIQUE` on `email` is a business rule: two customers may not share an address today, and if that rule changes you drop the constraint without touching a single foreign key.\n\nThat separation is the whole design. Identity is internal and stable; business rules sit beside it and are allowed to change.\n\n**Sequential id or UUID?** A `bigint` identity is small, sorts naturally, and produces compact indexes because new rows land at the end of the B-tree. A `uuid` can be generated on a phone that is offline, or by three services that never talk to each other, without collision: at the cost of 16 bytes instead of 8 and, for random UUIDs, writes scattered across the whole index, which measurably slows heavy insert workloads. Newer time-ordered UUID versions recover most of that locality. Pick a bigint unless you need to mint ids in several places.\n\n## Composite keys\n\nSome tables have no single identifying column, and inventing one is worse than admitting it:\n\n```sql\nCREATE TABLE enrolments (\n  student_id bigint REFERENCES students(id),\n  course_id  bigint REFERENCES courses(id),\n  enrolled_on date NOT NULL,\n  PRIMARY KEY (student_id, course_id)\n);\n```\n\nThat primary key is a statement of fact: a student is enrolled in a course once. If they can re-enrol next term, the key is wrong and the real key includes the term. Get this decision right and a whole family of duplicate-row bugs never happens.\n\n## Foreign keys and what happens on delete\n\n`REFERENCES students(id)` means the database checks, on every insert and update, that the student exists. It also decides what happens when the student is deleted, and this is a choice you must make rather than accept:\n\n```sql\nstudent_id bigint REFERENCES students(id) ON DELETE RESTRICT   -- refuse the delete\nstudent_id bigint REFERENCES students(id) ON DELETE CASCADE    -- delete the payments too\nstudent_id bigint REFERENCES students(id) ON DELETE SET NULL   -- orphan them deliberately\n```\n\n`RESTRICT` is the safe default for anything financial: you should not be able to make a year of payments vanish by tidying up a student record. `CASCADE` is right for rows that have no meaning alone: the items of a deleted draft order. `SET NULL` is right when the fact survives its owner, and it requires the column to be nullable, which every later query must then handle.\n\nThe single most common data-integrity failure in real systems is a foreign key that was never declared, \"for performance\". You save a few microseconds per insert and buy an unknown number of rows pointing at customers who no longer exist, discovered years later by a join that quietly returns fewer rows than it should.\n\n## Where a key does not exist yet\n\nSome data arrives with no reliable key at all: a spreadsheet of donations with names and dates and nothing else. You cannot conjure identity out of it. What you can do is be explicit: create a surrogate id for each row on import, keep the raw text, and treat \"is this the same person as that?\" as a separate, evidence-based step with its own lesson later in this course. The mistake is quietly assuming the name is a key, joining on it, and reporting the result."},"check":{"question":"A `customers` table uses `email` as its primary key, and `orders`, `tickets` and `invoices` all reference it. A customer asks to change her email address. What is the damaging consequence of this design?","options":[{"text":"Email addresses are case-sensitive in some systems, so the change may create a duplicate customer row that the unique constraint fails to catch, splitting her history in two.","correct":false,"tempting":"A real hazard, but it exists whether or not email is the primary key, and it is fixed with normalisation on write."},{"text":"The unique constraint will reject the new address if anybody else has ever used it.","correct":false,"tempting":"That would be a visible, immediate error the team can handle; the damaging failure here is the one that succeeds."},{"text":"The identifying value must be rewritten in every referencing table, and every earlier report or export can no longer be joined to current data.","correct":true},{"text":"Storing an email address as a key breaches data protection rules in most countries.","correct":false,"tempting":"Data protection governs how personal data is handled, not which column is a primary key; this states a legal conclusion the situation does not support."}]},"prev":{"slug":"rows-columns-and-types","title":"What a column type actually buys you","url":"https://addaly.com/learn/data-and-sql/rows-columns-and-types"},"next":{"slug":"one-fact-in-one-place","title":"Where each fact should live","url":"https://addaly.com/learn/data-and-sql/one-fact-in-one-place"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Keys: how a database knows one thing from another', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/keys-and-identity (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Keys: how a database knows one thing from another","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":1,"slug":"tables-types-and-the-question","title":"Tables, types and the question you are actually asking"},"answer":null,"keyIdea":"A primary key answers “is this the same thing as before?”, so it must be meaningless and permanent; a business rule such as a unique email sits beside it and is allowed to change.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-question-a-key-answers","runs":[{"text":"The question a key answers"}]},{"t":"p","runs":[{"text":"Two rows say "},{"text":"Ade Okafor","code":true},{"text":". Are they one person who ordered twice, or two people who share a common name? Nothing about the text can tell you. A key is the column, or set of columns, that settles it: the thing the database and the rest of your system agree means "},{"text":"this one, and no other","i":true},{"text":"."}]},{"t":"h2","id":"natural-keys-and-their-disappointments","runs":[{"text":"Natural keys and their disappointments"}]},{"t":"p","runs":[{"text":"A natural key is a value that already exists in the world and looks unique: an email address, a phone number, a national ID, a vehicle registration."}]},{"t":"p","runs":[{"text":"Each one fails in a way you will meet:"}]},{"t":"ul","items":[[{"text":"Email.","b":true},{"text":" People share them (family accounts), change them, and reuse them: an abandoned address gets reassigned by the provider a year later."}],[{"text":"Phone number.","b":true},{"text":" Recycled aggressively by telecoms. A number that was one customer’s is another’s within months."}],[{"text":"National ID.","b":true},{"text":" Not everybody has one, transcription errors are common, and storing it makes your database a target and a legal responsibility."}],[{"text":"A product’s SKU.","b":true},{"text":" Reissued by suppliers. Changed at rebrand."}]]},{"t":"p","runs":[{"text":"The deeper problem is not uniqueness, it is "},{"text":"stability","i":true},{"text":". If the key changes, every row that referenced it must change too, and any exported report becomes unjoinable to the present."}]},{"t":"h2","id":"surrogate-keys","runs":[{"text":"Surrogate keys"}]},{"t":"p","runs":[{"text":"So most tables use a surrogate key: a meaningless number the database generates and never changes."}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE customers (\n  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  email  text NOT NULL UNIQUE,\n  phone  text\n);"},{"t":"p","runs":[{"text":"Both properties are present and they are different. "},{"text":"PRIMARY KEY","code":true},{"text":" on "},{"text":"id","code":true},{"text":" is the internal identity: permanent, meaningless, what other tables point at. "},{"text":"UNIQUE","code":true},{"text":" on "},{"text":"email","code":true},{"text":" is a business rule: two customers may not share an address today, and if that rule changes you drop the constraint without touching a single foreign key."}]},{"t":"p","runs":[{"text":"That separation is the whole design. Identity is internal and stable; business rules sit beside it and are allowed to change."}]},{"t":"p","runs":[{"text":"Sequential id or UUID?","b":true},{"text":" A "},{"text":"bigint","code":true},{"text":" identity is small, sorts naturally, and produces compact indexes because new rows land at the end of the B-tree. A "},{"text":"uuid","code":true},{"text":" can be generated on a phone that is offline, or by three services that never talk to each other, without collision: at the cost of 16 bytes instead of 8 and, for random UUIDs, writes scattered across the whole index, which measurably slows heavy insert workloads. Newer time-ordered UUID versions recover most of that locality. Pick a bigint unless you need to mint ids in several places."}]},{"t":"h2","id":"composite-keys","runs":[{"text":"Composite keys"}]},{"t":"p","runs":[{"text":"Some tables have no single identifying column, and inventing one is worse than admitting it:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE enrolments (\n  student_id bigint REFERENCES students(id),\n  course_id  bigint REFERENCES courses(id),\n  enrolled_on date NOT NULL,\n  PRIMARY KEY (student_id, course_id)\n);"},{"t":"p","runs":[{"text":"That primary key is a statement of fact: a student is enrolled in a course once. If they can re-enrol next term, the key is wrong and the real key includes the term. Get this decision right and a whole family of duplicate-row bugs never happens."}]},{"t":"h2","id":"foreign-keys-and-what-happens-on-delete","runs":[{"text":"Foreign keys and what happens on delete"}]},{"t":"p","runs":[{"text":"REFERENCES students(id)","code":true},{"text":" means the database checks, on every insert and update, that the student exists. It also decides what happens when the student is deleted, and this is a choice you must make rather than accept:"}]},{"t":"code","lang":"sql","label":"SQL","text":"student_id bigint REFERENCES students(id) ON DELETE RESTRICT   -- refuse the delete\nstudent_id bigint REFERENCES students(id) ON DELETE CASCADE    -- delete the payments too\nstudent_id bigint REFERENCES students(id) ON DELETE SET NULL   -- orphan them deliberately"},{"t":"p","runs":[{"text":"RESTRICT","code":true},{"text":" is the safe default for anything financial: you should not be able to make a year of payments vanish by tidying up a student record. "},{"text":"CASCADE","code":true},{"text":" is right for rows that have no meaning alone: the items of a deleted draft order. "},{"text":"SET NULL","code":true},{"text":" is right when the fact survives its owner, and it requires the column to be nullable, which every later query must then handle."}]},{"t":"p","runs":[{"text":"The single most common data-integrity failure in real systems is a foreign key that was never declared, “for performance”. You save a few microseconds per insert and buy an unknown number of rows pointing at customers who no longer exist, discovered years later by a join that quietly returns fewer rows than it should."}]},{"t":"h2","id":"where-a-key-does-not-exist-yet","runs":[{"text":"Where a key does not exist yet"}]},{"t":"p","runs":[{"text":"Some data arrives with no reliable key at all: a spreadsheet of donations with names and dates and nothing else. You cannot conjure identity out of it. What you can do is be explicit: create a surrogate id for each row on import, keep the raw text, and treat “is this the same person as that?” as a separate, evidence-based step with its own lesson later in this course. The mistake is quietly assuming the name is a key, joining on it, and reporting the result."}]}],"check":{"question":[{"text":"A "},{"text":"customers","code":true},{"text":" table uses "},{"text":"email","code":true},{"text":" as its primary key, and "},{"text":"orders","code":true},{"text":", "},{"text":"tickets","code":true},{"text":" and "},{"text":"invoices","code":true},{"text":" all reference it. A customer asks to change her email address. What is the damaging consequence of this design?"}],"options":[{"text":[{"text":"Email addresses are case-sensitive in some systems, so the change may create a duplicate customer row that the unique constraint fails to catch, splitting her history in two."}],"correct":false,"tempting":[{"text":"A real hazard, but it exists whether or not email is the primary key, and it is fixed with normalisation on write."}]},{"text":[{"text":"The unique constraint will reject the new address if anybody else has ever used it."}],"correct":false,"tempting":[{"text":"That would be a visible, immediate error the team can handle; the damaging failure here is the one that succeeds."}]},{"text":[{"text":"The identifying value must be rewritten in every referencing table, and every earlier report or export can no longer be joined to current data."}],"correct":true,"tempting":null},{"text":[{"text":"Storing an email address as a key breaches data protection rules in most countries."}],"correct":false,"tempting":[{"text":"Data protection governs how personal data is handled, not which column is a primary key; this states a legal conclusion the situation does not support."}]}],"order":[1,2,0,3],"why":null},"prev":{"slug":"rows-columns-and-types","title":"What a column type actually buys you","minutes":8},"next":{"slug":"one-fact-in-one-place","title":"Where each fact should live","minutes":9}}}