Keys and Relationships Between Tables in SQL - Quiz

A key is a column or a set of columns that either identifies a row uniquely or links it to a row in another table. Keys work as constraints: the DBMS itself rejects queries that break uniqueness or refer to rows that do not exist.

In SQL you declare three kinds of keys:

• PRIMARY KEY – identifies a row: unique, never NULL, one per table;
• UNIQUE – forbids repeated values;
• FOREIGN KEY – links a row to another table.

The other names describe how a key was chosen and what it consists of: a candidate key is any column that could become the primary key; an alternate key is a candidate key not chosen as primary, declared as UNIQUE; a natural key comes from real-world data (email); a surrogate key is an artificial id; a composite key consists of several columns and only their combination is unique, for example PRIMARY KEY (topic_id, tag_id).

A natural key comes from real-world data, such as an email. A surrogate key is an artificial column with no business meaning, such as id INT AUTO_INCREMENT.

• A natural key can change: when a user changes the email, every reference to it has to be updated. A surrogate number is assigned once.
• In related tables a natural key takes a string of up to 100 characters in every reference; a surrogate key takes 4 bytes (INT) or 8 bytes (BIGINT).
• A natural key guarantees the uniqueness of the data itself; with a surrogate key the email needs a separate UNIQUE constraint.
• In Hibernate/JPA a surrogate key is the standard choice: a field with @Id and @GeneratedValue.

That is why Java applications almost always use a surrogate key. AUTO_INCREMENT numbers are not guaranteed to be consecutive: a failed insert or a rolled-back transaction can “use up” a value.

Both constraints forbid duplicates and create an index automatically, but:

• a table has one PRIMARY KEY and as many UNIQUE constraints as you need;
• PRIMARY KEY does not allow NULL, while a UNIQUE column can contain NULL unless it is declared NOT NULL;
• a foreign key usually refers to the PRIMARY KEY; a reference to UNIQUE is possible but rare.

UNIQUE protects alternate keys – a user’s login and email. In MySQL, PostgreSQL and Oracle a unique column can hold any number of NULL values: by the SQL standard two unknown values are not equal. SQL Server allows only one NULL – a common surprise when you move a schema.

A constraint can be named: CONSTRAINT uq_users_phone UNIQUE (phone). The name appears in error 1062 and is used to drop the constraint.

A foreign key is a column of the child table whose value must match the primary (or unique) key of a row in the parent table. The DBMS will not let you create a thread with a nonexistent author (error 1452) or delete a user who has threads (error 1451).

CONSTRAINT fk_topics_author FOREIGN KEY (author_id)
    REFERENCES users (id)

MySQL requirements:

• both tables use the InnoDB engine – MyISAM accepts the syntax but silently ignores the key;
• the column types match: INT refers to INT, not to BIGINT or INT UNSIGNED (otherwise error 3780); for strings the character set and collation match;
• the key refers to a PRIMARY KEY or UNIQUE key of the parent table (since MySQL 8.4 a reference to a non-unique index is rejected by default);
• the parent table is created before the child table (otherwise error 1824).

MySQL creates an index on the foreign key column itself if a suitable one does not exist. SET FOREIGN_KEY_CHECKS = 0 turns the check off, and references inserted meanwhile are not re-checked when it is turned back on.

ON DELETE and ON UPDATE define what to do with child rows when the parent row is deleted or its key is changed:

• RESTRICT – the operation is rejected with error 1451; for data that must not be lost (orders, payments);
• NO ACTION – in InnoDB works like RESTRICT and is the default;
• CASCADE – child rows are deleted or updated together with the parent; when they make no sense without it (messages of a thread, tags of a thread);
• SET NULL – the foreign key becomes NULL, the column must allow NULL; when the link is optional (the message stays, the author is unknown);
• SET DEFAULT – InnoDB will not create such a table, not used in MySQL.

CONSTRAINT fk_messages_topic FOREIGN KEY (topic_id)
    REFERENCES topics (id) ON DELETE CASCADE,
CONSTRAINT fk_messages_author FOREIGN KEY (author_id)
    REFERENCES users (id) ON DELETE SET NULL

CASCADE fires along the whole chain at every level, so use it only where child rows are really useless without the parent. ON UPDATE CASCADE is rarely needed: a surrogate id never changes; it is useful when a foreign key refers to a natural key, for example a country code.

There are three kinds:

• One-to-one (1:1), a user – their profile: a foreign key that is also the primary key or UNIQUE (user_profiles.user_id). Without UNIQUE you get one-to-many.
• One-to-many (1:M), a user – their threads: a foreign key on the “many” side (topics.author_id), not in users.
• Many-to-many (M:N), threads – tags: a single foreign key cannot express it, so you need a junction table with two foreign keys and a composite primary key.

CREATE TABLE topic_tags (
    topic_id INT NOT NULL,
    tag_id   INT NOT NULL,
    PRIMARY KEY (topic_id, tag_id),
    CONSTRAINT fk_topic_tags_topic FOREIGN KEY (topic_id)
        REFERENCES topics (id) ON DELETE CASCADE,
    CONSTRAINT fk_topic_tags_tag FOREIGN KEY (tag_id)
        REFERENCES tags (id) ON DELETE CASCADE
);

The composite key does not let you attach a tag to a thread twice. In JPA this relationship is described by @ManyToMany with @JoinTable.