Constraints and Keys in SQL: Runnable Examples, Failed Inserts, and Fixes

A key identifies a row or connects tables, while a constraint is a rule the database enforces. Warehouse, Product, and Stock expose successful inserts, rejected inserts, and a foreign-key delete action. Error wording varies by engine, so the important result is which rule accepts or rejects each operation.

SQL constraints and keys: what each one controls

A constraint is checked when a row is inserted or changed. The same declaration can protect valid values, row identity, or relationships:

DEFAULT supplies an omitted value; it does not validate a value that was supplied. An invalid state still fails its CHECK, and an explicit NULL still fails NOT NULL. UNIQUE rejects duplicate non-NULL values, while a required alternate key needs UNIQUE and NOT NULL together.

The starting rows are warehouses (10, 'North Depot') and (20, 'West Depot'), products 501/KB-01 and 502/MS-02, and stock batch (501, 'B-2026-01', 40).

Keys in SQL: super, candidate, primary, alternate, composite, and foreign

In Product(product_id, sku, product_name, unit_cost, warehouse_id, state), both {product_id} and {sku} are candidate keys because each is unique and minimal. Choose product_id as the primary key. The required UNIQUE sku becomes an alternate key. {product_id, sku} is a superkey but not a candidate key because either candidate key already identifies the row.

Stock(product_id, batch_code, quantity) uses the composite primary key (product_id, batch_code). Product 501 can appear in several batches, so product_id alone is insufficient. Batch code B-2026-01 can also be reused for another product, so batch_code alone is insufficient. Only the pair identifies a stock row in this design.

Product.warehouse_id and Stock.product_id are foreign keys. The first may be NULL because an unassigned product is valid; every non-NULL value must match a parent key. Stock.product_id is required because stock without a product has no meaning.

For attribute closures and the domain, entity, and referential integrity taxonomy, use Keys and Integrity Constraints in DBMS: Types, Rules and Worked Examples. The warehouse schema instead tests the SQL engine boundary through declarations, accepted rows, rejected rows, and one referential-action trace.

SQL CREATE TABLE example with named constraints

Create each parent before any table that references it:

CREATE TABLE Warehouse (
    warehouse_id INTEGER NOT NULL,
    warehouse_name VARCHAR(60) NOT NULL,
    CONSTRAINT pk_warehouse PRIMARY KEY (warehouse_id),
    CONSTRAINT uq_warehouse_name UNIQUE (warehouse_name)
);

CREATE TABLE Product (
    product_id INTEGER NOT NULL,
    sku VARCHAR(24) NOT NULL,
    product_name VARCHAR(80) NOT NULL,
    unit_cost DECIMAL(10, 2) NOT NULL,
    warehouse_id INTEGER,
    state VARCHAR(12) DEFAULT 'ACTIVE' NOT NULL,
    CONSTRAINT pk_product PRIMARY KEY (product_id),
    CONSTRAINT uq_product_sku UNIQUE (sku),
    CONSTRAINT ck_product_cost CHECK (unit_cost > 0),
    CONSTRAINT ck_product_state CHECK (state IN ('ACTIVE', 'RETIRED')),
    CONSTRAINT fk_product_warehouse FOREIGN KEY (warehouse_id)
        REFERENCES Warehouse(warehouse_id) ON DELETE SET NULL
);

CREATE TABLE Stock (
    product_id INTEGER NOT NULL,
    batch_code VARCHAR(16) NOT NULL,
    quantity INTEGER DEFAULT 0 NOT NULL,
    CONSTRAINT pk_stock PRIMARY KEY (product_id, batch_code),
    CONSTRAINT ck_stock_quantity CHECK (quantity >= 0),
    CONSTRAINT fk_stock_product FOREIGN KEY (product_id)
        REFERENCES Product(product_id)
);

Product.warehouse_id is nullable because deleting a warehouse sets that reference to NULL. The Stock key explicitly marks both components NOT NULL, which keeps the intended rule clear even on database engines with permissive composite-key behaviour. Named constraints also make the failing rule easier to identify.

SQL constraint checks: run valid and invalid inserts step by step

Insert parent rows first, followed by two valid products. Both products omit state, so both store ACTIVE:

INSERT INTO Warehouse (warehouse_id, warehouse_name)
VALUES (10, 'North Depot'), (20, 'West Depot');

INSERT INTO Product
    (product_id, sku, product_name, unit_cost, warehouse_id)
VALUES
    (501, 'KB-01', 'Keyboard', 25.00, 10),
    (502, 'MS-02', 'Mouse', 12.50, 20);

Run each rejected insert independently, so one error does not prevent the remaining tests:

INSERT INTO Product
    (product_id, sku, product_name, unit_cost, warehouse_id)
VALUES (503, 'KB-01', 'Compact Keyboard', 22.00, 10);

INSERT INTO Product
    (product_id, sku, product_name, unit_cost, warehouse_id)
VALUES (504, 'HD-04', 'USB Hub', 0, 10);

INSERT INTO Product
    (product_id, sku, product_name, unit_cost, warehouse_id)
VALUES (505, 'CM-05', 'Camera', 35.00, 99);

INSERT INTO Product
    (product_id, sku, product_name, unit_cost, warehouse_id)
VALUES (506, 'ST-06', NULL, 18.00, 10);

1. Product 503 repeats sku KB-01, so uq_product_sku rejects it.

2. Product 504 has unit_cost 0, so ck_product_cost rejects it.

3. Product 505 refers to warehouse 99, which does not exist, so fk_product_warehouse rejects it.

4. Product 506 supplies NULL for product_name, so the column's NOT NULL rule rejects it.

Test the composite key, second foreign key, and quantity check against Stock:

INSERT INTO Stock (product_id, batch_code, quantity)
VALUES (501, 'B-2026-01', 40);

INSERT INTO Stock (product_id, batch_code, quantity)
VALUES (501, 'B-2026-01', 15);

INSERT INTO Stock (product_id, batch_code, quantity)
VALUES (999, 'B-2026-02', 10);

INSERT INTO Stock (product_id, batch_code, quantity)
VALUES (502, 'B-2026-03', -1);

The first stock insert succeeds. The repeated pair fails pk_stock even though the quantity differs. Product 999 fails fk_stock_product, and quantity -1 fails ck_stock_quantity.

Foreign keys and referential actions: trace one delete exactly

Before deletion, Product 502 is (502, 'MS-02', 'Mouse', 12.50, 20, 'ACTIVE'). Run:

DELETE FROM Warehouse WHERE warehouse_id = 20;

ON DELETE SET NULL changes Product 502 to (502, 'MS-02', 'Mouse', 12.50, NULL, 'ACTIVE'). The product remains; only its warehouse reference is cleared. Product 501 is unchanged because it refers to warehouse 10.

RESTRICT or NO ACTION would reject the parent deletion while children refer to it, although the timing distinction is product-specific. CASCADE would delete matching children. SET NULL preserves them but requires a nullable foreign-key column. Choose the action from the meaning of the child row after its parent disappears.

The foreign key blocks orphan warehouse identifiers before any join runs. SQL Queries and Joins in DBMS: Worked Join and GROUP BY shows how valid references become predictable query rows.

SQL constraints and keys: common traps and precise fixes

Treating UNIQUE as PRIMARY KEY. A table has one primary key declaration but may have several UNIQUE constraints. NULL handling under UNIQUE varies by engine, so add NOT NULL when an alternate key must always exist.

Forgetting three-valued logic. CHECK (unit_cost > 0) does not by itself reject NULL because the expression is unknown. unit_cost DECIMAL(10, 2) NOT NULL closes that gap. A default still does not make a supplied NULL legal.

Adding constraints to dirty data. Find and repair duplicates, NULLs, invalid values, and orphan references first. Then add the named constraint and test one allowed operation plus one rejected operation. A composite foreign key must reference all columns of a matching parent key in the same order.

Constraints enforce legal states but do not remove dependency-based duplication. Normalization in DBMS: 1NF to BCNF Rules, Worked Example explains when a table should be decomposed before its rules are declared.

Constraints and keys in exams and interviews: patterns to practise

A complete answer should identify the rule, predict the result, and explain why. Practise these checks:

1. Identify both Product candidate keys and explain why their union is not minimal.

2. Predict which of the four Product inserts fails and name the violated constraint.

3. Prove that (product_id, batch_code) is minimal for Stock under the stated rules.

4. Trace Product 502's warehouse_id after warehouse 20 is deleted.

A useful DDL prompt asks for sku as a required alternate key and warehouse_id as an optional foreign key with ON DELETE SET NULL. The essential clauses are sku VARCHAR(24) NOT NULL, UNIQUE (sku), a nullable warehouse_id, and the named foreign key shown above.

Constraints and keys in SQL: the short version and next step

Define legal values, derive minimal candidate keys, choose the primary key, enforce required alternate keys with UNIQUE and NOT NULL, add foreign keys with intentional referential actions, then attack the schema with both legal and illegal rows.

For a structured DBMS sequence, continue with CS Fundamentals for Placements by Sanchit Sir. The CS Fundamentals for Exams & Placements category connects SQL constraints to the rest of core computer science.