CHECK Constraints in MariaDB — Let the Database Reject Impossible Rows
CHECK Constraints in MariaDB — Let the Database Reject Impossible Rows
A web form can reject a negative price. A PHP handler can check that an end date comes after a start date. But what happens when the same table is written by an import script, an administrator, a background job, or a second application?
This is the problem that a database constraint helps solve. Instead of asking every writer to remember the same row-level rule, we can put a durable part of that rule next to the data. In MariaDB, a CHECK constraint evaluates a condition when a row is inserted or updated. If the condition is false, the write is rejected.
That does not make application validation unnecessary. It gives the database a backstop for rules that must remain true regardless of which code path performs the write.
What a CHECK Constraint Is For
MariaDB documents four common table constraint families: PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK. They protect different properties:
PRIMARY KEYidentifies a row and is unique and non-null.UNIQUEprevents duplicate values in a key.FOREIGN KEYconnects a value to a row in another table.CHECKtests whether values in the row satisfy a condition.
So a check is a good fit for a row invariant: a statement that should be true for every stored row. A product price may not be negative. A quantity may not be below zero. An event's end time may not precede its start time.
This is narrower than “validate all input”. OWASP's input-validation guidance separates syntax and semantic validation from other controls such as parameterized SQL, output encoding, and authorization. A constraint participates in data integrity; it does not decide whether a user may edit a row or whether an HTML response is safe.
Column-Level and Table-Level Rules
MariaDB supports a short column-level form and a named table-level form. The first is convenient when a rule concerns one column:
CREATE TABLE inventory_item (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(120) NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
);
The conditions describe properties of one value. A negative price or stock count does not belong in this table, so the database can refuse it even if a new writer forgets to repeat the rule.
A table-level constraint is more expressive when several columns must be considered together. Naming it also makes later maintenance and error investigation clearer:
CREATE TABLE booking (
id INT PRIMARY KEY AUTO_INCREMENT,
starts_at DATETIME NOT NULL,
ends_at DATETIME NOT NULL,
CONSTRAINT booking_dates_ordered
CHECK (ends_at > starts_at)
);
The name is not just decoration. MariaDB can use it in an error message, and a named constraint gives a migration a stable target if the rule later needs to be removed or replaced.
CHECK Is Not the Same as NOT NULL
One easy mistake is to use a condition while leaving the column nullable. Consider this definition:
CREATE TABLE article_stat (
id INT PRIMARY KEY AUTO_INCREMENT,
view_count INT CHECK (view_count >= 0)
);
The rule describes non-negative counts, but it does not clearly express whether a missing count is allowed. If absence is invalid, say so explicitly:
CREATE TABLE article_stat (
id INT PRIMARY KEY AUTO_INCREMENT,
view_count INT NOT NULL CHECK (view_count >= 0)
);
This distinction matters because SQL has a value marker called NULL. It is not the same as zero, an empty string, or a missing HTTP field. A condition involving nullable values may not produce the simple true-or-false intuition we have from ordinary programming languages. If “unknown” should be rejected, make that policy explicit with NOT NULL or with a condition that deliberately handles NULL.
For optional dates, the intended rule might instead be “both dates are absent, or both are present and ordered”:
CONSTRAINT optional_booking_dates_valid
CHECK (
(starts_at IS NULL AND ends_at IS NULL)
OR (starts_at IS NOT NULL AND ends_at IS NOT NULL
AND ends_at > starts_at)
)
Whether that is the right policy is a domain decision. The important part is not letting nullable columns silently decide the policy for you.
What Happens on INSERT and UPDATE?
Checks apply to writes, not just to the original table definition. A row that was valid yesterday should not become invalid merely because an update changed one of its values.
For example, with the booking_dates_ordered rule, both of these operations need to satisfy the condition:
INSERT INTO booking (starts_at, ends_at)
VALUES ('2026-11-16 09:00:00', '2026-11-16 10:00:00');
UPDATE booking
SET ends_at = '2026-11-16 11:00:00'
WHERE id = 1;
If an insert or update makes the expression false, MariaDB rejects the operation. The application should treat that rejection as a data-integrity failure and return an appropriate response. It should not assume that a client-side check or an earlier PHP check guarantees that the database will accept the write.
Adding a Rule to an Existing Table
Constraints can be part of CREATE TABLE, but they can also be added with ALTER TABLE:
ALTER TABLE inventory_item
ADD CONSTRAINT inventory_item_stock_nonnegative
CHECK (stock >= 0);
That migration should be planned rather than pasted into production casually. Existing rows may already violate the proposed rule, and the table may be written by code that has not yet been updated to handle the new failure. First inspect and reconcile the data, then deploy the application behavior and constraint as one deliberate change.
To inspect the resulting definition, use the database's schema output:
SHOW CREATE TABLE inventory_item;
If a named rule is genuinely obsolete, MariaDB documents dropping it with ALTER TABLE ... DROP CONSTRAINT. Removing a constraint should be treated like removing a test: record why the invariant no longer belongs in the database, rather than hiding a failing import.
What CHECK Does Not Protect
A check constraint is deliberately limited. It does not replace the rest of the application's boundary checks:
- Parse the request and validate its shape before business logic uses it.
- Check authorization. A valid
article_iddoes not prove that the caller may edit that article. - Use parameterized queries. A constraint does not turn string-built SQL into safe SQL.
- Encode or sanitize output for its destination. A valid database value can still be dangerous in an HTML, JavaScript, URL, or log context.
- Give the user a useful validation message. A raw database error is usually too technical and may reveal internal names.
The application and database therefore have different jobs. PHP can explain that “end time must be later than start time” before a write is attempted. MariaDB can enforce the same invariant when another path reaches the table. Duplicating a rule at both layers is not necessarily wasteful: one layer improves feedback, while the other protects the stored state.
Keep the Rule Small and Deterministic
A constraint is easiest to reason about when it describes a stable property of the row. Good candidates are comparisons, ranges, required relationships between columns, and membership in a small allowed set.
Be more cautious with rules that depend on changing external state, hidden assumptions, or nondeterministic behavior. MariaDB's documentation discusses deterministic expressions and notes that constraint behavior needs care in replication environments. The database server version, SQL mode, storage engine, and deployment topology are part of the rule's real context.
MariaDB also documents a check_constraint_checks system variable that can disable expression checks, for example during a controlled data load. That is an operational escape hatch, not a normal application setting. If checks are disabled, invalid data can enter the table; the load process then owns the difficult task of finding and repairing it.
Conclusion
CHECK constraints are useful when a database must reject a row that violates a simple, durable rule. They are especially valuable when a table has more than one writer, because the rule no longer lives only in one form handler or one PHP function.
They are not a replacement for request validation, authorization, parameterized queries, or output safety. A practical design can use both: validate early for a clear user experience, then let MariaDB enforce the invariant at the last responsible moment. The remaining question is not “can this rule be written as SQL?” but “is this a fact that every stored row must obey?”
