Keys

Rows Need Stable IDs

A parent key gives child rows something exact to point at.

highlighted = computed this step

Rows need stable ids

A key column gives each parent row a stable value that other rows can point at.

key value identifies a row\text{key value identifies a row}

Show both tables

The parent table has 3 rows, and the child table has 3 rows.

parent rows=3\text{parent rows}=3

Key examples are tiny finite table checks; SQL dialects, indexing, query planning, deferrable constraints, enforcement timing, performance, and product behavior are out of scope.

Rows need stable ids: parent tablecustomer_idname1Ada2Ben3Cy Rows need stable ids: child tableorder_idcustomer_idtotal101150102230103170 key columnsparent keychild foreign keycustomer_idcustomer_id child-to-parent linkschildRowchildValueparentRowsstatus01[0]match12[1]match21[0]match duplicate parent keysvalueparentRowsnonenone dangling referenceschildRowvaluestatusnonenonenone key factsfactvalueparentRowCount3childRowCount3duplicateParentKeyCount0referenceCount3danglingReferenceCount0parentKeyUniqueyesvalidyesdeleteStatusnone

Read the key columns

The key-columns table names the parent id and the child foreign-key column.

parent key and child key\text{parent key and child key}

Summary

This book checks tiny table facts only; it does not model a database product.

finite key check\text{finite key check}