MariaDB / Basic: relational foundations / Lesson 3
Keys and relational integrity
Model customers and bookings with explicit identity and valid relationships. Make constraints express real rules rather than patching inconsistent records later.
On this page
Lesson goals
- Distinguish primary, unique and foreign keys.
- Choose referential actions deliberately.
Identity is stable; labels can change
A primary key gives every row a unique, non-NULL identity. A business code can also need a UNIQUE constraint, even when an integer key exists. Customer names are unsuitable keys because people share names and may change them. A foreign key expresses that a child references an existing parent. Normalization keeps the customer email in one authoritative place instead of copying it into every booking where future updates would create conflicting versions.
Write the full foreign-key definition
The two key columns use the same integer type and signedness. Both tables explicitly use InnoDB. A named table-level FOREIGN KEY makes the relationship visible and avoids historical shorthand REFERENCES behavior in older MariaDB versions. The parent primary key is indexed; InnoDB also needs an appropriate child index and can create one automatically. Restricting parent deletion preserves a booking until the application deliberately resolves the business relationship.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
email VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE bookings (
booking_id INT PRIMARY KEY, customer_id INT NOT NULL,
CONSTRAINT fk_booking_customer FOREIGN KEY (customer_id)
REFERENCES customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB;The email constraint and relationship solve different problems; neither replaces the primary keys.
Referential actions encode consequences
RESTRICT rejects a parent change that would leave dependent children invalid. CASCADE propagates a supported parent delete or key update to children, and can affect many rows. SET NULL requires a nullable child column and means that an absent relationship is valid. Choose from business semantics, not convenience during import. A nullable foreign key permits NULL without requiring a matching parent; use NOT NULL when every booking must always have a customer.
Read a valid miniature relationship
With empty tables from the previous definition, create the parent before the child. The join below then returns one booking and its current email. A second booking for customer 7 is permitted because the child foreign key is not unique. Reusing booking_id 101 would violate the primary key. Trying customer 99 would violate the relationship. These are distinct checks, so retain the actual error code rather than treating every rejected write as a duplicate.
INSERT INTO customers VALUES (7, 'sara@example.test');
INSERT INTO bookings VALUES (101, 7);
SELECT b.booking_id, c.email
FROM bookings AS b JOIN customers AS c
ON c.customer_id = b.customer_id
ORDER BY b.booking_id;booking_id | email
101 | sara@example.testExpected result for the two inserts; no real personal data is used.
Preserve constraints during change
Inspect SHOW CREATE TABLE when a constraint behaves unexpectedly. Imports should load parent rows before children or stage data for validation. Disabling foreign_key_checks can admit orphans; re-enabling it does not retroactively prove all existing rows valid. Use an anti-join audit after controlled migration work and fix the source mapping. Remember that uniqueness also follows collation rules, and a MariaDB UNIQUE index can contain multiple NULL values when its column permits them.
Common errors and fixes
ERROR 1452: Cannot add or update a child row
- Why it happens
- The child points to an absent parent.
- How to fix it
- Correct the identifier or insert the legitimate parent first.
- Prevent it next time
- Validate source mappings and load dependencies in order.
ERROR 1062: Duplicate entry
- Why it happens
- A primary or unique value already exists under its comparison rules.
- How to fix it
- Identify the named key and decide whether this is a retry or conflicting data.
- Prevent it next time
- Define idempotency and email normalization explicitly.
ERROR 1005: Cannot create table; errno 150
- Why it happens
- Foreign-key definitions may mismatch types, engines or indexed parent columns.
- How to fix it
- Compare effective definitions and inspect the detailed InnoDB diagnostic.
- Prevent it next time
- Declare matching types and full named constraints in reviewed migrations.
Check your understanding
1. A customer can have many bookings. Should bookings.customer_id be UNIQUE?
Show worked solution
No. That uniqueness would allow at most one non-NULL booking per customer. Keep booking_id as the primary key, customer_id as a non-NULL foreign key, and use a nonunique child index for lookup. The parent customer_id remains unique, so each booking still identifies exactly one customer.
2. A legacy import disabled constraints. Find orphan bookings without deleting anything.
Show worked solution
Left join each booking to its customer and retain rows whose parent key is NULL. For a non-NULL child key, every returned booking is an orphan needing source investigation. With valid fixture rows the expected result is empty. Do not infer that an empty query proves other constraints such as unique emails.
SELECT b.booking_id FROM bookings AS b
LEFT JOIN customers AS c ON c.customer_id = b.customer_id
WHERE c.customer_id IS NULL;Key takeaways
- A key expresses identity; a foreign key expresses a relationship.
- Choose cascade behavior from the consequences the business accepts.