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.
Search Keys
- sql check constraint
- sql check
- sql syntax checker
- check constraint
- check sql
- alter table check constraint
- sql constraint check
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;