Web Development

Foreign Keys in MariaDB - Let the Database Reject Impossible Relationships

Written by Adam Muiz Updated: 12 Oct 2026 10 min read
Foreign Keys in MariaDB - Let the Database Reject Impossible Relationships

A post row says its author is user 42, but user 42 does not exist. The application can hide the missing name, return an error, or quietly show “unknown author,” yet none of those responses repairs the relationship stored in the database. The harder question is where that impossible state should be rejected.

Application validation is useful, but a foreign key places the rule at the shared data boundary. Every writer that uses the database must then respect the same relationship, whether the write comes from PHP, a maintenance script, an import, or a second application. That is a strong guarantee, but a narrow one: a foreign key can prove that a referenced row exists. It cannot prove that the current user may reference it, that the content is valid, or that deleting it is wise.

This article develops that boundary for a small PHP application using MariaDB and InnoDB. The important work is not merely adding FOREIGN KEY. It is deciding what the relationship means, what should happen when its parent disappears, and how to introduce enforcement without ignoring data that is already inconsistent.

The constraint describes one invariant

Suppose an application stores authors and posts. The parent table owns author identifiers; the child table stores one of those identifiers in posts.author_id. According to the MariaDB foreign-key documentation, each non-NULL child value must match a value in the referenced parent key.

CREATE TABLE authors (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id BIGINT UNSIGNED NULL,
  title VARCHAR(191) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_posts_author
    FOREIGN KEY (author_id) REFERENCES authors (id)
    ON DELETE SET NULL
    ON UPDATE RESTRICT
) ENGINE=InnoDB;

With this definition, an insert that supplies an unknown non-NULL author_id is rejected. MariaDB documents that case as error 1452 with SQLSTATE 23000. The constraint also has a deliberate name. MariaDB can generate one, but a stable name makes error messages, schema inspection, and later migrations easier to understand.

The example allows author_id to be NULL. That is a data-model choice, not a foreign-key requirement. Here it means a post may survive without a current author relationship. If every post must always have an author, the column should be NOT NULL and the deletion policy must preserve that rule.

Why a PHP existence check is not the same guarantee

A handler can query authors before inserting a post. That produces a friendlier validation path, but it does not replace the constraint. Another writer may omit the check. More subtly, the author can be deleted after the check and before the insert. The two statements observe different moments unless the application coordinates them with appropriate database rules and transaction behavior.

The foreign key evaluates the relationship where the write is accepted. Application validation can still explain the problem before attempting the write, while the database remains the final integrity boundary. This is the same reason a form may check whether a username looks available while a unique constraint decides whether two concurrent registrations can actually claim it.

That does not make every database error a user-interface message. A raw constraint name or SQL statement belongs in protected diagnostics, not in a public response. The application should translate an expected conflict into language from its own domain, such as “Choose an existing author,” while unexpected failures follow normal server-side error handling.

Deletion actions are policies, not convenience switches

The most consequential line is often ON DELETE. MariaDB supports RESTRICT, CASCADE, and SET NULL for ordinary InnoDB foreign keys. In MariaDB, NO ACTION is a synonym for RESTRICT, and an omitted action defaults to RESTRICT. Those details are product-specific; another database can give NO ACTION different timing semantics.

  • RESTRICT rejects deletion of a parent while children still reference it. This is a cautious default when the child has an independent history or deletion requires an explicit workflow.
  • CASCADE deletes matching children automatically. It fits rows that are truly components of the parent, such as items inside a temporary draft that has no meaning once the draft is deleted.
  • SET NULL preserves each child but removes the relationship. It requires nullable child columns and a truthful interpretation of “no current parent.”

The PostgreSQL constraints documentation offers a useful general design distinction: cascading is appropriate when the child is a component that cannot sensibly exist alone, while restriction is more suitable when parent and child represent independent objects. That is conceptual guidance, not MariaDB behavior documentation.

For published content, automatic deletion may be too destructive even if it is technically valid. Preserving a post with a nullable author, preventing author deletion, or using an application-level deactivation state can each be more honest. A cascade should encode a settled lifecycle rule, not save one extra DELETE statement.

The columns have to agree

A relationship can be conceptually correct and still fail at schema level. MariaDB requires the parent and child to use the same supported storage engine and compatible column definitions. For integer keys, size and sign must match. A BIGINT UNSIGNED parent should not be paired with a signed BIGINT child. For string keys, character set and collation must also match.

The referenced parent columns must be indexed, and the child columns need a BTREE index or the leftmost part of one. InnoDB may create the child index when needed, but relying on that side effect can hide index intent. Composite relationships require the columns in the correct order. Prefix indexes, and therefore ordinary TEXT or BLOB prefixes, cannot serve as foreign-key columns under the documented MariaDB rules.

Foreign keys also do not imply NOT NULL. MariaDB permits a child row with a NULL foreign-key value. The schema must use nullability to say whether “no relationship” is valid rather than assuming the constraint answers that question.

Existing data must pass before the rule can help

Adding a constraint to an established application begins with observation. First inspect the real table definitions with SHOW CREATE TABLE, including engine, key types, signs, character sets, collations, and existing indexes. Then look for child rows that would violate the proposed relationship:

SELECT p.id, p.author_id
FROM posts AS p
LEFT JOIN authors AS a ON a.id = p.author_id
WHERE p.author_id IS NOT NULL
  AND a.id IS NULL;

An empty result supports adding the constraint; it does not by itself select the right deletion action. A non-empty result is evidence that the application needs a data decision. Depending on the domain, the correct repair may be restoring a missing parent, mapping the child to a valid parent, setting an optional reference to NULL, quarantining the row for review, or deleting data that is demonstrably invalid. Choosing one automatically without understanding the records merely makes the query quiet.

Once the data and definitions agree, the migration can add the named constraint:

ALTER TABLE posts
  ADD CONSTRAINT fk_posts_author
  FOREIGN KEY (author_id) REFERENCES authors (id)
  ON DELETE SET NULL
  ON UPDATE RESTRICT;

This is production DDL, not a harmless text edit. The MariaDB ALTER TABLE documentation notes that the statement can wait for a metadata lock and that available algorithms and locking strategies depend on the operation, engine, and server version. On a busy or large table, inspect the installed version's documentation, test against representative data, plan a rollback, and observe the migration rather than assuming it will be instant.

Do not use disabled checks as a cleanup strategy

MariaDB exposes foreign_key_checks, and disabling it can be useful in controlled bulk-loading or schema procedures. It is also an easy way to create data that the declared relationship would normally reject. Turning checks back on is not a substitute for an orphan audit, and the MariaDB documentation warns that adding a constraint while checks are disabled may leave existing rows unvalidated.

A safer migration story is explicit: inventory relationships, detect violations, decide how each class of violation should be repaired, perform the DDL under a planned operational window, and verify the resulting metadata. If a restore process temporarily changes checking behavior, its validation step belongs in the restore design rather than in an unwritten assumption.

Let PHP handle the domain, not parse database prose

PDO uses exception mode by default from PHP 8.0, according to the PHP manual on PDO error handling. A PDOException exposes SQLSTATE information and driver-specific details. That gives an application enough structure to log the failure and, where the operation has a known conflict path, return a controlled domain response.

Avoid building core behavior by matching the human-readable text of an error. Message wording contains engine details and can change. Even SQLSTATE 23000 describes an integrity-constraint class rather than one business condition, so driver-specific codes or a prior domain check may still be needed to distinguish an unknown parent from another constraint failure. The database remains authoritative, while the application remains responsible for useful language, authorization, and safe disclosure.

Transactions still matter when one operation changes several rows. A foreign key prevents a broken reference; it does not make a multi-step publish, billing, or notification workflow atomic. Nor can it enforce a relationship to an object that lives only in another service. Those boundaries need their own protocol, reconciliation, or state model.

Verify the declared relationship

After deployment, verify more than one successful insert. A compact test plan includes:

  • Insert a child with a valid parent and confirm success.
  • Insert or update a child with an unknown parent and confirm rejection.
  • Exercise the chosen parent deletion behavior and inspect every affected table.
  • Confirm whether NULL is accepted or rejected as the model requires.
  • Test concurrent application paths that create or remove related records.
  • Inspect the installed definition with SHOW CREATE TABLE.
  • Query information_schema.KEY_COLUMN_USAGE to confirm the constrained and referenced columns.
  • Verify that public errors reveal no SQL, constraint names, or private record details.

Tests should also cover backup restoration and schema migrations. A constraint that exists only in the live database but not in migration files or verified backups is an operational accident waiting to be rediscovered.

Conclusion

A MariaDB foreign key gives a small PHP application one valuable promise: a non-NULL reference cannot point to a parent row that the database cannot find. Placing that promise in InnoDB protects the relationship across every writer, including the one a future maintenance script might forget to validate.

The keyword is the easy part. The durable design comes from matching column definitions, deciding whether absence is allowed, choosing deletion behavior from the data's lifecycle, cleaning existing orphans, and treating ALTER TABLE as an operational change. Foreign keys do not replace application validation, authorization, transactions, or thoughtful deletion. They make one invariant explicit and enforceable. That narrower claim is exactly what makes them useful.

References