SQL CHECK Constraint


The SQL CHECK constraint is used to limit the value range that can be placed in a table field or column.

The SQL CHECK constraint allows you to enables a condition on each new row entered in a table. If the condition evaluates to false, the row isn't entered into the table.


SQL CHECK Constraint on CREATE TABLE

The following SQL statement creates a SQL CHECK constraint on the "BookPrice" field when the "Books" table is created. The SQL CHECK constraint specifies that the field "BookPrice" must only include integers greater than 100.

For MySql


CREATE TABLE Books
(
BookID INT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2) NOT NULL,
BookDomain VARCHAR(255),
CHECK (BookPrice > 100)
);

For Microsoft SQL Server / Oracle / Microsoft Access


CREATE TABLE Books
(
BookID INT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2) NOT NULL CHECK (BookPrice > 100),
BookDomain VARCHAR(255)
);

In the above example, the following SQL statement creates a new table called Books and adds five fields.

Here, BookPrice field has a SQL CHECK constraint to validate newly entered records.

To insert records on "Books" table, use the following SQL statements:


INSERT INTO Books (BookID, BookName, AuthorName, BookPrice, BookDomain)
VALUES (111, 'Sql Complete Programming','Suresh Babu', 160, 'Programming');

INSERT INTO Books (BookID, BookName, AuthorName, BookPrice, BookDomain)
VALUES (222, 'Sql Database Hacking', 'Vinoth Kumar', 75, 'Hacking');

INSERT INTO Books (BookID, BookName, AuthorName, BookPrice, BookDomain)
VALUES (333, 'Pl Sql Quick Reference','Siva Kumar', 220, 'Programming');

INSERT INTO Books (BookID, BookName, AuthorName, BookPrice, BookDomain)
VALUES (444, 'Sql Database Analysis','Suresh Babu', 180, 'Database');

The SQL statement above would insert three(but there are four records tried to insert) new records into the "Books" table.

The second record will not inserted into table. Bcoz the record doesn't match the criteria of the BookPrice column. The value of BookPrice column must have greater than "100".

After executing above query, the "Books" table look like this:

BookId BookName AuthorName BookPrice BookDomain
111 Sql Complete Programming Suresh Babu 160 Programming
333 Pl Sql Quick Reference Siva Kumar 220 Programming
444 Sql Database Analysis Suresh Babu 180 Database

To allow naming of a SQL CHECK constraint, and for defining a SQL CHECK constraint on multiple fields, use the following SQL syntax:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


CREATE TABLE Books
(
BookID INT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2) NOT NULL,
BookDomain VARCHAR(255),
CONSTRAINT chk_Price CHECK (BookPrice > 100 AND BookDomain = 'Database')
);


SQL CHECK Constraint on ALTER TABLE

To create a SQL CHECK constraint on the "BookPrice" field when the table is already created, use the following SQL statement:

For MySql / Microsoft SQL Server / Oracle / Microsoft Access


ALTER TABLE Books
ADD CHECK (BookPrice > 100);

To create a SQL CHECK constraint on multiple fileds when the table is already created, use the following SQL statement:

For MySql / Microsoft SQL Server / Oracle / Microsoft Access


ALTER TABLE Books
ADD CONSTRAINT chk_Price CHECK (BookPrice > 100 AND BookDomain = 'Database');


To DROP a SQL CHECK Constraint

To drop or remove a SQL CHECK constraint, use the following SQL statement:

For MySQL


ALTER TABLE Books
DROP CHECK chk_Price;

For Microsoft SQL SERVER / Microsoft ACCESS / Orcale


ALTER TABLE Books
DROP CONSTRAINT chk_Price;