MySQL NOT NULL and CHECK

How it works

A CHECK constraint evaluates a row condition. In MySQL, a condition that becomes UNKNOWN because of NULL does not by itself reject the row. Add NOT NULL when the value must be known. The seed therefore requires quantity and checks that it is nonnegative. This lesson uses a tiny separate table so accepted and rejected cases can be tested without damaging the stock fixture. Database constraints protect all clients, not only one form.

Run in a disposable MySQL lab

Use Oracle MySQL 8.4 with InnoDB, not Bhylu’s SQLite playground. The examples were reviewed on MySQL 8.4.8. XAMPP may provide MariaDB, which has different feature and collation behaviour. Check your actual server before running the script. These lessons do not require a change to Bhylu’s PHP, Apache or normal database service.

1. On a separate lab instance, create the empty bhylu_mysql_lab schema. Stop if it already exists; do not overwrite an existing database.
2. Select that schema and import the complete repair-parts seed once. It sets the documented session mode, utf8mb4 collation, UTC and English date-name locale.
3. Run this lesson in a fresh copy of the seed, not after another lesson’s writes. Use the command-line client or a compatible editor. DELIMITER lines are client instructions for stored-program examples.

Download this complete lesson script. Its setup is the linked seed; review the dataset before changing it. Administrative and destructive examples are restricted to the specifically named disposable objects. Every script repeats the session settings because each new connection has its own session. No MySQL browser runner is currently available.

Example

SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET SESSION time_zone = '+00:00';
SET SESSION lc_time_names = 'en_US';
CREATE TABLE checked_stock (
  id INT PRIMARY KEY,
  quantity INT NOT NULL,
  CONSTRAINT ck_stock_nonnegative CHECK (quantity >= 0)
) ENGINE=InnoDB;
INSERT INTO checked_stock VALUES (1,0),(2,5);
SELECT id, quantity FROM checked_stock ORDER BY id;

Expected result

1 | 0
2 | 5

Zero is accepted because an empty shelf is a valid stock state. Negative stock and NULL are rejected for different reasons. Give constraints meaningful names so a failure can be traced to the intended rule. Application validation can give friendly feedback, while the database remains the final integrity boundary. More complex multi-row invariants may require transactions rather than a single-row CHECK.