SQL Constraints

-- How the SQL Standard constrains updates

A constraint is an object which tells the DBMS: don't permit updates of the database which would break rules established for business/integrity reasons.

A typical CHECK constraint works thus:
CREATE TABLE TABLE_1 (COLUMN_1 SMALLINT);
-- make the table

ALTER TABLE TABLE_1 ADD CONSTRAINT CONSTRAINT_1 CHECK (COLUMN_1 > 0);
-- make the constraint

INSERT INTO TABLE_1 VALUES (5);
-- this succeeds

UPDATE TABLE_1 SET COLUMN_1 = -5;
-- this fails

In the above example, we made a table and then we made a CHECK constraint on the table (alternative: it's possible to do both steps in one statement). The CHECK is that COLUMN_1 must be greater than 0. The INSERT statement puts +5 in; that's okay. The UPDATE statement tries to put -5 in; that's not okay, so the statement fails.

A non-typical CHECK constraint, with a deferral, works thus:
CREATE TABLE TABLE_1 (COLUMN_1 SMALLINT);

ALTER TABLE TABLE_1 ADD CONSTRAINT CONSTRAINT_1 CHECK (COLUMN_1 > 0) DEFERRABLE INITALLY DEFERRED;

INSERT INTO TABLE_1 VALUES (5);

UPDATE TABLE_1 SET COLUMN_1 = -5;

COMMIT;

In the above example, the UPDATE succeeds even though the constraint is "violated". However, the COMMIT fails. You can get away with violations of deferred constraints for a while, but eventually you must either fix the data, or ROLLBACK the transaction.

Our examples thus far have used constraints which are subsidiary to base tables -- the most common kind of constraints. There are also general constraints, which can be made with the CREATE ASSERTION statement.

A typical PRIMARY KEY constraint works thus:
CREATE TABLE TABLE_1 (COLUMN_1 SMALLINT);

ALTER TABLE TABLE_1 ADD CONSTRAINT CONSTRAINT_1 PRIMARY KEY (COLUMN_1);

INSERT INTO TABLE_1 VALUES (5);

A table's primary key must contain unique values, so the second INSERT in this example will fail. Alternative: if all we want to say is "values must be unique", we could say UNIQUE (COLUMN_1) instead of PRIMARY KEY (COLUMN_1).

A typical FOREIGN KEY constraint works thus:

CREATE TABLE TABLE_1 (COLUMN_1 SMALLINT);

ALTER TABLE TABLE_1 ADD CONSTRAINT CONSTRAINT_1 PRIMARY KEY (COLUMN_1);

INSERT INTO TABLE_1 VALUES (5);

CREATE TABLE TABLE_2 (COLUMN_1 SMALLINT);

ALTER TABLE TABLE_2 ADD CONSTRAINT FOREIGN KEY (COLUMN_1) REFERENCES TABLE_1;

INSERT INTO TABLE_2 VALUES (5);

INSERT INTO TABLE_2 VALUES (6);

The first INSERT into TABLE_2 will succeed, because our foreign-key value -- 5 -- exists in the primary-key table that we're referencing. But we never inserted a 6 into TABLE_1, so we can't insert a 6 into TABLE_2, so the second INSERT in this example will fail.

Return to Ocelot home page