CHECK Constraint SQL

The CHECK Constraint is used in table creation and adjustment. It sets limits on a specific column within the table. It can restrict the inclusion of either numbers or characters.

Say we wish to create a table of fictional characters, but do not want to include any from the Twilight series. The following statement will do the trick in most languages. For mySQL put the CHECK constraint at the end.

CREATE TABLE fiction_chars
(
id INT NOT NULL,
name VARCHAR (255),
description TEXT,
book VARCHAR (255) CHECK (book != 'Twilight'),
author VARCHAR (255),
PRIMARY KEY(id)
)

It should be noted here that the != means "not equal". It can also be written <>. In some database documentation <> is preferred. Nevertheless, != seems more logical. Most database programmers will be able to choose between them except in a very few cases where <> is the only "not equal" operator available.

If a constraint is to be added later it should be done like, this actually naming the constraint:

ALTER TABLE fiction_chars ADD CONSTRAINT twi_con CHECK (book != 'Twilight')

The Constraint may be DROPped. Interestingly the word "CHECK" is not used in the syntax. Instead we have:

ALTER TABLE fiction_chars DROP CONSTRAINT twi_con

Adding and subtracting constraints or columns later can be problematic when maintaining a backup or template of a database. Be sure that the template includes the added structure.

< BETWEEN Operator SQL | CREATE INDEX SQL >


Send notes in disappearing ink!

Interesting Pages