SQL Foreign Key: Verify a Reference and a Rejected Unknown ID

The practice task

Create a fictional workshop directory and one enrolment referring to a known workshop. Then test an enrolment that names a missing workshop ID so the integrity rule has an observable failure case.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

PRAGMA foreign_keys = ON;
CREATE TABLE workshops(id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE enrolments(id INTEGER PRIMARY KEY, workshop_id INTEGER NOT NULL REFERENCES workshops(id));
INSERT INTO workshops VALUES (1,'SQL practice');
INSERT INTO enrolments VALUES (10,1);

SELECT id, workshop_id FROM enrolments ORDER BY id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The valid enrolment refers to workshop 1 and remains readable. The deliberately invalid statement below refers to workshop 99, which is absent. It was separately executed against this fixture and rejected with a foreign-key constraint error.

The NOT NULL rule is separate from the reference rule. A nullable foreign-key column can represent an absent relationship under its engine rules; declaring the field required expresses a different requirement from merely checking a supplied ID.

The example does not specify a cascading deletion action. Do not assume that deleting a workshop should delete its enrolments, or that every database enables enforcement in the same way. Choose and verify the intended update and deletion behavior in the actual schema.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Enable and verify enforcement for the engine and connection you use.
• Keep required-field and valid-reference rules distinct.
• Test an unknown referenced identifier, not only a valid pair.

Deliberate rejected input

This separate statement was checked against the fixture and rejected with: FOREIGN KEY constraint failed.

INSERT INTO enrolments VALUES (11,99);