Referential Integrity You Can Actually Rely On

Objective

Every integrity rule that lives in application code is a rule each new service has to re-implement, and one of them eventually will not. A background job, a migration script, an admin tool, an LLM agent writing SQL — each is a chance to insert an order for a customer who does not exist.

From v2.0.0-ce these rules live in the schema and the engine enforces them for every client, including the ones your team has not written yet.

Read this before upgrading. Earlier versions accepted FOREIGN KEY and table-level UNIQUE declarations and never checked them. If you have been running on an older build, you may already hold rows that violate constraints you believe are active. v2.0 refuses new violations; it does not clean up existing data. Audit before you rely on it.

Step 1: A schema that states its own rules

-- Drop child first: the parent cannot be dropped while it is referenced.
DROP TABLE IF EXISTS recipe_ri_orders;
DROP TABLE IF EXISTS recipe_ri_customers;

CREATE TABLE recipe_ri_customers (
  customer_id  INT PRIMARY KEY,
  email        TEXT UNIQUE,
  name         TEXT
);

CREATE TABLE recipe_ri_orders (
  order_id     INT PRIMARY KEY,
  customer_id  INT REFERENCES recipe_ri_customers(customer_id),
  total        DOUBLE
);

Two rules are now facts about the data rather than hopes about the code: an email appears at most once, and every order belongs to a real customer.

Step 2: Seed two customers and one valid order

INSERT INTO recipe_ri_customers (customer_id, email, name) VALUES
  (1, 'ada@example.com', 'Ada Lovelace'),
  (2, 'bo@example.com',  'Bo Nilsson');

INSERT INTO recipe_ri_orders (order_id, customer_id, total) VALUES
  (10, 1, 99.00);

Expected: both succeed. Order 10 points at customer 1, which exists.

Step 3: Ground truth — the orphan is refused

This is the statement that silently succeeded before v2.0:

INSERT INTO recipe_ri_orders (order_id, customer_id, total) VALUES (11, 99, 50.00);

-- Constraint violation: Foreign key violation:
-- 'recipe_ri_orders' has no matching row in 'recipe_ri_customers'

The statement FAILS, and that is the result. Customer 99 does not exist, so the order cannot.

Customer 99 does not exist, so the order cannot. The error names both tables, which is what you want at 3am.

Why these three are not sql blocks. Steps 3, 4 and 5 are supposed to fail, so they are shown as plain text with the engine's real error beneath them. The certification harness executes only ```sql blocks, and a recipe whose point is three refusals would otherwise fail certification forever. Paste them into your own session to see the refusals first-hand.

Step 4: The duplicate key is refused too

INSERT INTO recipe_ri_customers (customer_id, email, name) VALUES (3, 'ada@example.com', 'Ada Clone');

-- Constraint violation: Duplicate key:
-- row violates a UNIQUE or PRIMARY KEY constraint on table 'recipe_ri_customers'

FAILS.

A different primary key is not enough — email is declared UNIQUE, and that is now checked. This is the constraint that stops two sign-up paths creating the same account twice.

Step 5: A referenced parent cannot be deleted

The subtler half of referential integrity. Deleting a customer who has orders would leave those orders pointing at nothing:

DELETE FROM recipe_ri_customers WHERE customer_id = 1;

-- Constraint violation: Foreign key violation:
-- 'recipe_ri_orders' has no matching row in 'recipe_ri_customers'

FAILS with the same foreign-key violation. Customer 1 has order 10, so the delete is refused rather than quietly orphaning it.

To delete the customer you must deal with the orders first — which is exactly the conversation the constraint exists to force.

Step 6: Confirm nothing leaked through

SELECT
  (SELECT COUNT(*) FROM recipe_ri_customers) AS customers,
  (SELECT COUNT(*) FROM recipe_ri_orders)    AS orders;

Expected: 2 customers and 1 order. Three statements tried to corrupt this data and all three were refused. Customer 3 was never created, order 11 was never created, and customer 1 is still there with its order intact.

Step 7: Audit an existing database before trusting it

On a database that ran on an older build, find the rows that should not exist:

SELECT o.order_id, o.customer_id
FROM recipe_ri_orders o
LEFT JOIN recipe_ri_customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Expected here: zero rows — the engine never let one through. Run the same shape against your real tables after upgrading. Anything it returns predates enforcement and needs a decision: delete the orphans, or create the parents.

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_ri_orders;
DROP TABLE IF EXISTS recipe_ri_customers;

Child before parent, for the reason Step 5 demonstrates.

Use it from your agent

• REST/SDK: nothing to configure. Constraints apply to POST /v1/query/execute, the MySQL wire protocol and the SDKs identically, because they are enforced in the engine rather than in any client.
• Expect the error: code that previously inserted freely may now fail. That is the feature. Catch the constraint violation and surface it, rather than retrying.
• Why in-DB: an LLM agent writing SQL cannot be relied on to maintain referential integrity. The schema can.

Key Concepts Learned

• FOREIGN KEY and UNIQUE are enforced from v2.0. Before that they parsed and were ignored, which is worse than not supporting them.
• Enforcement runs in three directions: inserting a child with no parent, inserting a duplicate key, and deleting a parent that still has children.
• Upgrading does not clean existing data. Audit with a LEFT JOIN … IS NULL before you depend on the guarantee.
• Drop children before parents, for the same reason the delete was refused.
• A rule in the schema is enforced for every client. A rule in application code is enforced only by the clients that remember it.