Constraints In Microsoft SQL Server 2000 - Check Constraints
(Page 7 of 8 )

The nice thing about Check constraints is that we are not restricted to a particular column. We can also relate them to a column, but in essence they are table-related, which means they can check one column against another as long as all the columns belong to the same table and the values must be for the same row being updated or inserted.  Here are some typical cases where Check constraint will come in handy:

1. Limit Month column - use SQL command “BETWEEN 1 AND 12”
2. SSN  format - use SQL command LIKE ‘[0-9][0-9][0-9]- [0-9][0-9][0-9]- [0-9][0-9][0-9][0-9]'
3. For a city column check against a specified list, use CHECK(city) IN (“NY”, “FL”, “CA”, “MA”, )

In the following example (11) we are using Check constraint to check if the column min_lvl is greater than or equal to 10 and max_lvl less than or equal to 250.

CREATE TABLE Jobs
(job_id smallint IDENTITY(1,1) 
PRIMARY KEY CLUSTERED,job_desc varchar(50)NOT NULL, min_lvl tinyint NOT NULL 
CHECK (min_lvl >= 10),max_lvl tinyint NOT NULL CHECK (max_lvl <= 
250))



So while we try to insert any values in the min_lvl column less than 10, it will show an error. Similarly for max_lvl column any value greater than 250 will raise an error.

Creating A Check Constraint on an Existing Table  

We have a table called Customer and it has a field already defined as “DateInSystem GETDATE()” this field gets its date based on the current system date. Alter it to include a check constraint.

Example 12: If we try to enter any date less than the current date it will show an error.


Use Accounting
ALTER TABLE Customers
ADD CONSTRAINT 
Chk_DateSys
CHECK
(DateInSystem <= GETDATE())



A column can have any number of Check constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple Check constraints for a column are validated in the order created. The search condition must evaluate to a Boolean expression and cannot reference another table. A column-level Check constraint can reference only the constrained column, and a table-level Check constraint can reference only columns in the same table. Check constraints and rules serve the same function of validating the data during Insert and Delete statements. When a rule and one or more Check constraints exist for a column or columns, all restrictions are evaluated.

Next: Default Constraints >>
 

More Database Code Articles
More By Gayathri Gokul

Comments On: Constraints In Microsoft SQL Server 2000

>>> Be the FIRST to comment on this article!