SQL - Constraints
Overview
Constraints enforce data rules. We cover NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, FOREIGN KEY.
CREATE TABLE t (
id INT PRIMARY KEY,
code VARCHAR(10) UNIQUE,
qty INT NOT NULL,
price DECIMAL(10,2) DEFAULT 0.0,
CHECK (qty >= 0)
);
CREATE TABLE t (
id INT PRIMARY KEY,
code NVARCHAR(10) UNIQUE,
qty INT NOT NULL,
price DECIMAL(10,2) CONSTRAINT df_price DEFAULT(0.0),
CONSTRAINT ck_qty CHECK (qty >= 0)
);
CREATE TABLE t (
id INTEGER PRIMARY KEY,
code TEXT UNIQUE,
qty INTEGER NOT NULL,
price NUMERIC DEFAULT 0.0,
CHECK (qty >= 0)
);
Live Demo (SQLite)
| id | code | qty | price |
|---|---|---|---|
| 1 | A | 5 | 0 |
SQLSTATE[23000]: Integrity constraint violation: 19 CHECK constraint failed: qty >= 0