You might want to place any number of restrictions on the data that can be entered in your table – to see if data is in proper range or the correct format, for example that SQL cannot possibly account for beforehand. For this reason SQL provides the CHECK constraint, which allow you to define a condition that a value entered into the table must satisfied before it can be accepted. The CHECK constraint consists of the keyword CHECK followed by a parenthesized predicate, which employee the column(s) in question. Any attempt to update to or insert column value that will make this predicate FALSE will be rejected -- note that the predicate must actually be FALSE, not merely UNKNOWN.
We can use a CHECK constraint to restrict a column to specify values and thereby reject mistakes. For example, suppose the only cities in which we had sales offices were DELHI, NOIDA, ALLAHABAD, LUCKNOW and JAUNPUR. As long as we know that all of our sales people will be operating from one of these offices, we need not allow other values to be entered. If nothing else, using a restriction such as this will prevent typographical and similar errors from being accepted. Here is how we would restrict a column to specify values:
- CREATE TABLE SALESPEPOLE
- (SNUM INT NOT NULL PRIMARY KEY ,
- SNAME VARCHAR(50),
- CITY VARCHAR(30) CHECK
- (CITY IN ('DELHI', 'NOIDA','ALLAHABAD','LUCKNOW','JAUNPUR')),
- COMM DECIMAL CHECK (COMM<1)
- )