bhylu.com

Advertisements

SQL Constraints

Constraints reject data that violates rules you have chosen. They protect the table even when a different application or a manual script writes to it. Good application validation and database constraints work together.

Understand the idea

This stock adjustment table requires a product reference and a nonzero change. The foreign key verifies the product exists, while CHECK rejects an adjustment that changes nothing. A primary key identifies each adjustment. Each constraint has a separate job; combining them makes the stored record meaningful without depending on one particular user interface.

Where to try it

Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.

SQLite sample data · MySQL / MariaDB sample data · See the sample tables

A worked example

SQLite
Try in playground

CREATE TABLE stock_adjustments (
  adjustment_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(product_id),
  delta INTEGER NOT NULL CHECK (delta <> 0)
);
INSERT INTO stock_adjustments VALUES (1, 2, 3);
SELECT * FROM stock_adjustments;

Read the result

The valid adjustment records a change of 3 for product 2. A zero delta would fail the CHECK, and an unknown product would fail the foreign key when enforcement is enabled.

Try one change

Try a zero delta in a separate run and read the constraint error. Then restore a valid nonzero adjustment.