Primary and Foreign Keys

A primary key identifies a row using one or more columns. When enforced, it requires the complete key to be unique and its columns to be non-NULL. A table can exist without a primary key declaration; declaring one makes the identity rule explicit to the database.

For order lines, order_id alone repeats. The pair (order_id, line_number) can be a composite primary key: (O1, 1) and (O1, 2) are different lines. Two identical pairs violate the rule even if their other values differ. A text key does not automatically reject an empty string; that requires an additional rule.

A foreign key declares a reference to a primary or other eligible unique key, including a key in the same table. Many orders can refer to the same customer, so a foreign key is not itself a uniqueness rule. Declare NOT NULL separately when the reference must be present; composite references also require attention to the engine’s NULL rules.

When the same entity arrives from several systems

The rules above assume one system issues the identifiers. Integrating several breaks that assumption immediately: the CRM and the billing system both have a customer_id of 1001, and they are different people. A key is only unique inside the system that issued it, so the identifier that works across systems is the pair — the source system plus its local identifier.

Linking them is then a matter of a separate table rather than an edit to either source. A crosswalk holds one row per source record, mapping (source_system, source_id) to a master_id, and the master record is derived from the rows that point at it. Nothing overwrites the originals, which is what makes the link reviewable.

source_system	source_id	master_id	decided
crm	1001	M-77	rule: email match
billing	1001	M-91	rule: no match
billing	4420	M-77	manual, 2026-03-02

Reversing a link is the case worth thinking through, because it is where “just undo it” fails. Undoing a merge does not restore anything in the sources — they were never changed. It changes which master_id a source row maps to, and then everything derived from the old grouping has to be rebuilt: the master record itself, and any aggregate that counted those two rows as one customer. Keeping the reason and the date on each mapping row is what makes that rebuild possible, and the reason a manual decision belongs in this table rather than in a comment is that the next reprocessing run reads the table.

Two consequences for the keys themselves. The master identifier is a key you issue, so it should not be borrowed from a source whose identifiers can be retired or reissued on that source’s own schedule — a master_id that is really the CRM’s id becomes wrong the moment that record is retired. And what downstream tables need is a way back to the record at its source: storing that pair and resolving it to the master identifier through the crosswalk at read time is the most direct design, and a fact that stores the master identifier can still be re-linked later if its source identity is recoverable from a retained record, as described below.

That second point is where a re-linking succeeds or fails, and it is easy to get backwards. Editing one crosswalk row changes what a lookup returns; it does not change a master_id already written into a fact table. So the two designs behave differently:

• Facts keep the source pair and resolve through the crosswalk. A re-linking takes effect immediately for every fact, because nothing downstream stored the grouping.
• Facts store the master_id directly — often for query speed. Then a re-linking is a data migration: every affected fact row has to be updated or rebuilt, and to know which rows move you need each fact’s source identity — kept alongside the master id, or recoverable from a retained source record or ledger keyed by the fact’s own identifier. A fact that holds only master_id, with no such record anywhere, cannot be re-split, because after a merge nothing is left to say which side each row came from.

So if facts are going to store the master identifier, make sure each fact’s source identity stays recoverable — storing the source pair with it is the simplest way, and a retained source record keyed by the fact is another — and keep the crosswalk’s history. That is the difference between an unmerge you can perform and one you can only describe.

Enforced keys can reject an orphan order but cannot establish that a customer is real or an amount is correct. Joins can be written without foreign-key declarations and with conditions other than equality. Verify the actual constraints and matching multiplicity before relying on a relationship.

Further reading

• DuckDB Documentation, Constraints

See the worked examples in SQL from Zero to Working.