Constraints
A constraint is a rule the rows must keep; a change that would break one is refused, SQLSTATE 23000, the statement undone. Constraints are written with a table (CREATE TABLE), added and dropped by ALTER TABLE.
The examples are of the tables of Getting Started, and of teams and their members:
CREATE TABLE TEAMS (ID INTEGER PRIMARY KEY, LEAD INTEGER NOT NULL);
CREATE TABLE MEMBERS (ID INTEGER PRIMARY KEY, TEAM INTEGER NOT NULL,
CONSTRAINT IN_TEAM FOREIGN KEY (TEAM) REFERENCES TEAMS DEFERRABLE INITIALLY DEFERRED);
COMMIT;
OK
OK
OK
NOT NULL and UNIQUE
NOT NULL refuses the null value. UNIQUE refuses two rows of the same values in its columns; rows with a null in them are never alike.
INSERT INTO MEMBERS (ID) VALUES (13);
turborel: SQLCODE -201 (SQLSTATE 23000): column TEAM of ANN.MEMBERS is NOT NULL [X3.135-1986 §6.3 SR3, GR1]
Primary and foreign keys
A PRIMARY KEY is UNIQUE and NOT NULL: one row of each key, the table's way to name a row. A FOREIGN KEY, or REFERENCES, refuses a row whose values match no row of the table it references, and says what is done to the rows that refer to one deleted or changed: refused, the default; CASCADE, deleted or changed with it; SET NULL or SET DEFAULT.
INSERT INTO TEAMS VALUES (1, 10);
INSERT INTO TEAMS VALUES (1, 11);
OK
turborel: SQLCODE -201 (SQLSTATE 23000): PRIMARY KEY ANN.TEAMS_PRIMARY1 (ID) of ANN.TEAMS would be violated: a row of these values is there [X3.135-1986 §6.4 GR2]
TEAMS has a team 1 already when the second is inserted. A constraint
not named is given a name, as TEAMS_PRIMARY1. The foreign key IN_TEAM
is checked at COMMIT (Deferred checking).
CHECK constraints
A CHECK refuses a row its condition is false of; one it is unknown of, as of a null, passes. Its condition may read other tables, through a subquery.
UPDATE EMPLOYEES SET SALARY = -1 WHERE ID = 3;
turborel: SQLCODE -201 (SQLSTATE 23000): CHECK ANN.EMPLOYEES_CHECK1 of ANN.EMPLOYEES is false for a row [ISO/IEC 9075:1992 §11.6 GR3]
Assertions
An assertion is a constraint of a schema, on no one table: its condition holds of the database whole, checked after each statement that changes a table it reads (CREATE ASSERTION). That no row has a condition, or that a count of rows, or their greatest or least value, stays within a bound, is checked from the rows the statement changed, not the tables whole, as CREATE ASSERTION tells; so is that no rows of a join, or no group of a table's rows, have one; and a SUM or an average of exact numbers within a bound, from totals the assertion keeps.
Deferred checking
A constraint is checked at the end of each statement, unless it is deferred: then at COMMIT. A constraint DEFERRABLE INITIALLY DEFERRED is deferred in each transaction; SET CONSTRAINTS defers one DEFERRABLE, or makes one deferred IMMEDIATE. So rows that refer to each other may be inserted in either order:
INSERT INTO MEMBERS VALUES (20, 2);
INSERT INTO TEAMS VALUES (2, 20);
COMMIT;
INSERT INTO MEMBERS VALUES (21, 3);
COMMIT;
SET CONSTRAINTS IN_TEAM IMMEDIATE;
INSERT INTO MEMBERS VALUES (22, 4);
OK
OK
OK
OK
turborel: SQLCODE -913 (SQLSTATE 40002): the transaction is rolled back: a row of ANN.MEMBERS references no row of ANN.TEAMS: FOREIGN KEY ANN.IN_TEAM [ISO/IEC 9075:1992 §11.8 GR2] [ISO/IEC 9075:1992 §14.3 GR5a]
OK
turborel: SQLCODE -201 (SQLSTATE 23000): a row of ANN.MEMBERS references no row of ANN.TEAMS: FOREIGN KEY ANN.IN_TEAM [ISO/IEC 9075:1992 §11.8 GR2]
Member 20 came before its team, and was checked as the transaction committed, team 1 with it. Member 21's team 3 never came: the COMMIT failed, and rolled the transaction back. Made IMMEDIATE, the constraint refused member 22 at once.
Triggers
A trigger is an action its table runs each time a statement changes it: to keep a record of the changes, to keep another table in step, or to give a row a value before it is written. CREATE TRIGGER makes one, of INSERT, DELETE or UPDATE, to run BEFORE the rows are written or AFTER the statement, once for each row or once for the statement:
CREATE TABLE MOVES (MEMBER INTEGER, FROM_TEAM INTEGER, TO_TEAM INTEGER);
CREATE TRIGGER MOVED AFTER UPDATE OF TEAM ON MEMBERS
REFERENCING OLD AS O NEW AS N FOR EACH ROW
INSERT INTO MOVES VALUES (N.ID, O.TEAM, N.TEAM);
UPDATE MEMBERS SET TEAM = 1 WHERE ID = 20;
SELECT * FROM MOVES;
OK
OK
OK
MEMBER | FROM_TEAM | TO_TEAM
--------------+--------------+--------------
20 | 2 | 1
(1 row)
The trigger saw member 20 as it was, O, and as it is, N. It runs whoever changes MEMBERS, with the privileges of the owner of its schema, and what it does is undone with the statement if it fails. A view that cannot take a change can be given an INSTEAD OF trigger that does what the change means.