Can a Job Refer to a Pump We Have Never Recorded?

The maintenance team opens an inspection for P99. The job has a valid-looking ID and duration, but P99 is absent from the equipment register. A later join quietly loses the job.

We need the database to preserve the relationship while accepting writes from every application that uses it. Constraints make those rules part of the table rather than a convention in one import script.

Check duration and parent identity together, then retain the failing job IDs.

A form can validate an asset ID, but a batch importer may bypass the form. A constraint checks the data at the shared write boundary. It also protects against two writers that both passed a preliminary application check before either committed.

Keep friendly validation in the interface so users can repair mistakes. Treat database constraint failures as expected error outcomes too. The interface cannot safely assume that the state remained unchanged between its first check and its write.

The database rejects the violating change; the application still needs to explain which request failed.

A CHECK expression rejects a row when it evaluates to false. An unknown result from NULL does not act like false in this setting. CHECK(minutes >= 0) therefore needs NOT NULL when every job must have a duration.

A UNIQUE constraint handles duplicate keys, but null treatment varies by dialect and declaration. Specify the intended null policy separately. Our job IDs and asset references must always exist, so both are explicitly not null.

A constraint is a precise predicate. Read its NULL behaviour rather than relying on the wording of the column name.

REFERENCES assets(id) connects the child’s asset_id to the parent’s declared unique identity. Insert the parent before an immediately checked child. The child column’s type and representation should match the identifier contract.

The SQLite runner enables PRAGMA foreign_keys = ON before starting learner work. Changing this pragma inside an active transaction does not enable enforcement for that transaction. Production connections need deliberate initialisation and a verification check.

A foreign key establishes existence. It does not establish that the current user is allowed to use that asset.

CREATE TABLE assets(id TEXT PRIMARY KEY NOT NULL); INSERT INTO assets VALUES('P7'),('P8');

CREATE TABLE visits(id TEXT PRIMARY KEY NOT NULL,asset_id TEXT NOT NULL REFERENCES assets(id));
INSERT INTO visits VALUES('V3','P8');
SELECT v.id,a.id FROM visits v JOIN assets a ON a.id=v.asset_id;

Deleting an asset with historical jobs may need to be forbidden. ON DELETE RESTRICT expresses that policy here. CASCADE can be appropriate when dependent rows have no independent meaning, but it can also erase useful history if chosen casually.

SET NULL requires a nullable child reference and changes the meaning of the relationship. Often an equipment register should mark an asset retired rather than delete it. Choose based on the records’ lifetimes and audit requirements.

A referential action is a business decision that the database executes automatically.

A row-level CHECK can validate minutes or a status. Rules such as one active assignment per asset may need a unique or partial unique index. More complex cross-row conditions can require carefully designed transactions or triggers.

Do not hide a lookup of another table inside an apparently local check and assume every future change will revalidate it. Use the database mechanisms whose enforcement scope matches the invariant. PostgreSQL and SQLite differ in supported forms, so inspect the chosen dialect.

Constraints are strongest when their intended scope is easy to state and test.

The exercise inserts one valid job, then its tests try a missing parent, a duplicate key, a negative duration and a null duration. Each failure reveals a different missing constraint.

After a failed statement, inspect transaction state before deciding whether to continue or roll back. Engines and conflict policies differ. Our test harness isolates each attempted invalid insert in a savepoint so a rejection does not contaminate the next test.

A display containing one good row cannot prove that the next bad row will be rejected.