MariaDB Tutorial / Define Input Rules with NULL, DEFAULT, NOT NULL, and CHECK

NULL represents missing or unknown information and differs from zero or an empty string. DEFAULT supplies a value when an input is omitted, NOT NULL rejects NULL, and CHECK tests a value against a condition; combine them because each solves a different validation problem.

Separate NULL, an empty string, zero, and omission

A business may choose to treat an uncollected note and an empty note alike, but that is a data policy. Automatically replacing every NULL with an empty string removes the ability to distinguish those states. This exercise uses ordinary string handling and does not enable compatibility modes such as EMPTY_STRING_IS_NULL. Distinguish these rules from uniqueness in Identify Rows with PRIMARY KEY, AUTO_INCREMENT, and UNIQUE and from querying stored NULL values in Compare NULL Correctly with IS NULL and COALESCE.

Most ordinary comparisons with NULL produce an unknown result, represented by NULL, rather than true or false. Use IS NULL to test for it, not = NULL. The NULL-value reference explains the operators and exceptions.

Use each constraint for its own responsibility

Do not use a default to invent unknown information. If stock has not been counted, zero may incorrectly mean sold out. This example deliberately defines a new item’s initial stock as zero; another business may need NULL or an independent verification status.

Application checks can provide useful messages, while database constraints protect the storage boundary when another application, client, or SQL file writes data. Both layers have a purpose; validating only the visible form does not constrain every writer.

Run a complete example of defaults and nullable values

Connect as course_user to the practice server prepared in the installation guides, with the shopcourse database available. The command-line client and SQL-file guide explains the connection and SOURCE command. Run these checks inside that connection, not in the operating-system shell:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

The selected database should be shopcourse. Save a complete normal example as a UTF-8 .sql file and run it with SOURCE and its actual local path. Keep the intentionally failing statements separate: an error can stop a source file. Use a learning instance, not a connection containing production data.

The normal file inserts only valid rows. It saves the session mode and CHECK setting, then explicitly enables strict mode and CHECK evaluation. Check the actual server version when working with old installations rather than assuming that accepting CHECK syntax always proves enforcement.

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET @saved_check_constraints = @@SESSION.check_constraint_checks;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET SESSION check_constraint_checks = ON;
CREATE TABLE validation_lab (
  item_id INT PRIMARY KEY,
  item_name VARCHAR(100) NOT NULL,
  note VARCHAR(100) NULL DEFAULT NULL,
  stock INT NOT NULL DEFAULT 0,
  CONSTRAINT ck_validation_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO validation_lab (item_id, item_name, note) VALUES
  (1, 'Notebook', NULL),
  (2, 'Mug', '');
SELECT item_id, note IS NULL AS note_is_null,
       note = '' AS note_is_empty, stock
FROM validation_lab ORDER BY item_id;
CREATE TABLE nullable_check_lab (
  item_id INT PRIMARY KEY,
  stock INT NULL,
  CONSTRAINT ck_nullable_stock CHECK (stock >= 0)
) ENGINE=InnoDB;
INSERT INTO nullable_check_lab VALUES (1, NULL);
SELECT item_id, stock IS NULL AS stock_is_null FROM nullable_check_lab;

The first note is NULL, so its comparison with an empty string is also NULL. The second note is an empty string, making IS NULL false and the empty-string comparison true. Both inserts omit stock, so both receive the default zero.

The separate nullable_check_lab accepts a NULL stock value. Its CHECK expression does not evaluate to false for NULL. If stock must always be known and nonnegative, specify NOT NULL as well as CHECK; a nonnegative comparison alone is insufficient.

Test NOT NULL and CHECK failures separately

After the valid example, execute these statements one at a time. The first supplies NULL for a required name; the second supplies a name but violates the nonnegative-stock constraint:

INSERT INTO validation_lab (item_id, item_name) VALUES (3, NULL);

INSERT INTO validation_lab (item_id, item_name, stock) VALUES (3, 'Desk lamp', -1);

Expect a cannot-be-null error for the first and a constraint failure identifying the stock rule for the second. After each, SELECT COUNT(*) FROM validation_lab; should remain two. The failed item identifier 3 was not saved, so it can be reused in the second failure test. Exact error wording can vary by version and output language; identify the column or constraint that failed.

A default, the input form, and the type’s rules determine whether an explicit NULL is allowed, rejected, or handled specially. AUTO_INCREMENT and TIMESTAMP have special NULL behavior in some cases, so do not generalize this ordinary note-and-stock example to every type.

Inspect blank values, named rules, and enforcement settings

A NOT NULL string may still contain an empty string, as the string-type discussion explains. A rule such as CHECK (CHAR_LENGTH(TRIM(item_name)) > 0) can additionally reject empty names and names consisting of ordinary spaces. Do not assume default TRIM behavior removes every Unicode whitespace character or invisible glyph.

SHOW CREATE TABLE validation_lab;
SELECT @@SESSION.sql_mode, @@SESSION.check_constraint_checks;

The name ck_validation_stock makes a failed rule easier to locate. Use meaningful names that identify the table, column, or condition, and check naming scope when supporting different server versions. The constraint reference documents CHECK syntax and enforcement controls.

Disabling check_constraint_checks can permit data that violates the declared checks. Fix the input or the intended rule instead of disabling validation merely to make a write succeed. Switching checks on also does not mean that every previously stored invalid row has been reviewed or repaired.

Restore settings and repeat only the disposable exercise

SET SESSION check_constraint_checks = @saved_check_constraints;
SET SESSION sql_mode = @saved_sql_mode;

Restore these session settings in the same connection that saved the variables. If you disconnected, those variables no longer contain the original values; inspect the new connection instead of applying an empty saved variable.

Before repeating the exercise, use SELECT DATABASE(); and SHOW CREATE TABLE table_name;, substituting each actual table name, to confirm that the targets are the disposable tables created here. Back up any data you need to keep. Only after verifying those targets, remove them with:

DROP TABLE shopcourse.validation_lab, shopcourse.nullable_check_lab;

This removes both the named tables and their rows. A data-definition statement such as DROP TABLE is not undone by an ordinary ROLLBACK. You can then rerun the complete normal example. The existing products table from the first product-table exercise is deliberately outside this reset.