PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, DEFAULT, and Generated/Virtual Columns

Learning outcomes

ServiceHub can now represent its values, but representation alone does not stop impossible states. Without constraints, an application bug can insert a work order for a nonexistent customer, duplicate an external ticket identifier, store a negative labor duration, or persist a derived total that no longer agrees with its source columns. Relying only on application validation is fragile because scripts, imports, multiple services and future code paths can bypass one application layer.

MariaDB constraints turn schema design into a correctness contract. On the InnoDB baseline, PRIMARY KEY and UNIQUE identify rows and candidate keys, FOREIGN KEY enforces parent/child relationships, CHECK validates predicates, DEFAULT supplies values when a write omits them, and generated columns compute values from expressions. Each rule also has operational consequences for indexes, locking, DDL ordering, data loads and migrations.

1. PRIMARY KEY is identity plus an InnoDB storage decision

A PRIMARY KEY is unique and implicitly NOT NULL. In InnoDB, the primary key also has physical consequences because InnoDB organizes table rows around a clustered index. That means primary-key width and stability affect secondary indexes and update cost; Chapter 07 explores the storage internals. At the schema-contract level, the rule is simpler: choose a stable row identity and prove it is unique.

CREATE TABLE servicehub_sandbox.customers (  customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  external_ref VARCHAR(50) NOT NULL,  display_name VARCHAR(160) NOT NULL,  PRIMARY KEY (customer_id),  CONSTRAINT uq_customer_external_ref UNIQUE (external_ref)) ENGINE=InnoDB;

The surrogate customer_id provides compact internal identity, while external_ref is also protected as a business candidate key. Do not choose between “natural” and “surrogate” keys by slogan; Lesson 4 examines the tradeoff directly.

2. UNIQUE allows multiple NULLs unless NOT NULL says otherwise

MariaDB UNIQUE indexes reject duplicate non-NULL values, but a nullable unique column can contain multiple NULLs. That is consistent with SQL’s treatment of NULL as “unknown,” but it surprises developers who use UNIQUE as though it automatically means “exactly one value or nothing.” If the business rule requires every row to have a value, declare NOT NULL as a separate rule.

CREATE TABLE servicehub_sandbox.unique_probe (  id INT PRIMARY KEY,  optional_code VARCHAR(20) NULL,  CONSTRAINT uq_optional_code UNIQUE (optional_code)) ENGINE=InnoDB;INSERT INTO servicehub_sandbox.unique_probe VALUES (1,NULL),(2,NULL);INSERT INTO servicehub_sandbox.unique_probe VALUES (3,'A-100');-- Expected duplicate-key error:INSERT INTO servicehub_sandbox.unique_probe VALUES (4,'A-100');

If the business requirement is “every asset must have exactly one unique code,” use both NOT NULL and UNIQUE. If the requirement is “code may be unknown, but known codes must be unique,” nullable UNIQUE is appropriate.

3. FOREIGN KEY protects relationships, but requirements are exact

A foreign key is a child-table constraint that requires referenced parent values to exist. Current MariaDB documentation requires compatible child/parent types; for integer columns, size and sign must match. The referenced parent columns must be indexed, and the child columns need a BTREE index (which InnoDB can create automatically when necessary). Parent and child must use the same supporting storage engine and cannot be temporary or partitioned tables in this foreign-key model.

CREATE TABLE servicehub_sandbox.work_orders (  work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  customer_id BIGINT UNSIGNED NOT NULL,  external_ticket VARCHAR(64) NOT NULL,  status VARCHAR(20) NOT NULL DEFAULT 'new',  PRIMARY KEY (work_order_id),  CONSTRAINT uq_work_order_ticket UNIQUE (external_ticket),  CONSTRAINT fk_work_order_customer    FOREIGN KEY (customer_id)    REFERENCES servicehub_sandbox.customers(customer_id)    ON UPDATE RESTRICT    ON DELETE RESTRICT) ENGINE=InnoDB;

RESTRICT makes deletion of a referenced customer fail while work orders remain, which is usually safer than silently deleting operational history. CASCADE and SET NULL can be correct for other domains, but they are business semantics—not cleanup shortcuts. A cascade can touch many rows and interact with locks/replication, so choose it intentionally.

-- No customer 999999 exists: expected foreign-key error.INSERT INTO servicehub_sandbox.work_orders  (customer_id, external_ticket)VALUES (999999,'INC-BROKEN-001');

4. CHECK turns predicates into write-time contracts

A CHECK constraint evaluates an expression before a row is accepted. On the current baseline, CHECK constraints are enforced. Name important checks so failures point to a business rule rather than an anonymous generated name. MariaDB also exposes check_constraint_checks, but disabling checks is an administrative migration tool with risk: it does not magically validate old rows later.

ALTER TABLE servicehub_sandbox.work_orders  ADD COLUMN estimated_minutes INT NULL,  ADD CONSTRAINT chk_estimated_minutes    CHECK (estimated_minutes IS NULL OR estimated_minutes BETWEEN 1 AND 10080),  ADD CONSTRAINT chk_work_order_status    CHECK (status IN ('new','assigned','in_progress','done','cancelled'));-- Expected CHECK failure:UPDATE servicehub_sandbox.work_ordersSET estimated_minutes = -15WHERE work_order_id = 1;

The database can now reject an impossible negative estimate regardless of whether the write came from the web API, an ETL script or an interactive session. That is the value of placing invariant rules close to the data. Application validation remains useful for friendly error messages; database constraints provide the final shared boundary.

5. DEFAULT is what happens when the column is omitted

A DEFAULT supplies a value when an INSERT omits the column or explicitly requests DEFAULT. It is not a general-purpose repair mechanism for invalid data. Make deterministic business choices explicit: a status may default to new, and a creation timestamp may default to the current timestamp, but an unknown customer should not default to an arbitrary ID.

ALTER TABLE servicehub_sandbox.work_orders  ADD COLUMN created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  ADD COLUMN updated_at TIMESTAMP(6) NOT NULL    DEFAULT CURRENT_TIMESTAMP(6)    ON UPDATE CURRENT_TIMESTAMP(6);SHOW CREATE TABLE servicehub_sandbox.work_orders;

Default and automatic timestamp semantics are version-sensitive and should be visible in SHOW CREATE TABLE. A migration reviewer should verify the actual generated DDL rather than assuming a client ORM emitted the intended definition.

6. Generated columns derive data: VIRTUAL versus PERSISTENT

A generated column cannot normally be set to an arbitrary independent value; MariaDB computes it from an expression. VIRTUAL (the default) computes the value when read, while PERSISTENT—also called STORED—stores the computed result. The choice affects storage, read/write cost, indexing and DDL behavior. Generated-column support also varies by storage engine and expression restrictions, so verify the target version/engine rather than copying expressions blindly.

ALTER TABLE servicehub_sandbox.work_orders  ADD COLUMN sla_bucket VARCHAR(12)    AS (      CASE        WHEN estimated_minutes IS NULL THEN 'unplanned'        WHEN estimated_minutes <= 60 THEN 'short'        WHEN estimated_minutes <= 480 THEN 'standard'        ELSE 'extended'      END    ) VIRTUAL;SHOW FULL COLUMNS FROM servicehub_sandbox.work_orders;SELECT work_order_id, estimated_minutes, sla_bucketFROM servicehub_sandbox.work_orders;

The generated value cannot drift away from estimated_minutes because it has no independent source of truth. Use this pattern for genuinely derived data. Do not hide essential business workflow logic in a complex generated expression merely to avoid application code; maintainability and migration compatibility still matter.

7. Migration order: constraints turn existing data into a precondition

Adding a constraint to a populated table is a data-quality operation as well as DDL. If old rows violate the proposed rule, the migration may fail or require a deliberate cleanup phase. Foreign keys also impose dependency order: parent tables/keys must exist before child constraints, and teardown must reverse those dependencies unless constraints are explicitly removed.

SELECT wo.customer_id, COUNT(*) AS orphan_rowsFROM servicehub_sandbox.work_orders AS woLEFT JOIN servicehub_sandbox.customers AS c  ON c.customer_id = wo.customer_idWHERE c.customer_id IS NULLGROUP BY wo.customer_id;

8. Deliberately wrong approach: disable integrity and “fix it later”

A common import shortcut is to set FOREIGN_KEY_CHECKS=0, load data in arbitrary order, re-enable the variable, and assume MariaDB has retroactively validated every relationship. That assumption is unsafe. Disabling enforcement is a controlled administrative technique, not proof of integrity. The safe pattern is to stage/import in a disposable environment, run explicit orphan/duplicate/check queries, and only then promote or add constraints.

9. Hands-on lab and verification checklist

1. Create customers and work_orders in servicehub_sandbox.
2. Prove nullable UNIQUE accepts multiple NULLs but rejects duplicate non-NULL values.
3. Insert one real customer and one valid work order.
4. Attempt an orphan child insert and record the foreign-key error.
5. Add CHECK constraints; attempt an invalid status or duration.
6. Add timestamp defaults and inspect SHOW CREATE TABLE.
7. Add the VIRTUAL sla_bucket column and verify derived results.
8. Run the orphan preflight query and confirm zero violations before cleanup.

10. Summary and bridge

Constraints make a MariaDB schema reject states the business says cannot exist. PRIMARY KEY and UNIQUE protect identity/candidate keys, foreign keys protect relationships, CHECK protects predicates, DEFAULT supplies intentional omitted values, and generated columns keep derived values tied to source expressions. They also create index, DDL, migration and dependency consequences that must be planned.

The next lesson focuses on one constraint-adjacent design question with outsized operational impact: how identifiers are allocated. You will compare AUTO_INCREMENT, sequence objects, natural keys, surrogate keys and application-generated IDs without promising impossible gapless numbering.