Defining Constraints
Constraints are defined during the CREATE TABLE or ALTER TABLE command. They are used to enforce data integrity and ensure that valid data is entered into the columns. Constraints can also ensure that integrity is maintained between tables. A column-level constraint applies to a single column, whereas a table-level constraint applies to multiple columns, even if it does not apply to every column. PRIMARY KEY, UNIQUE, FOREIGN KEY, DEFAULT, and CHECK are all examples of constraints. Both PRIMARY KEY and UNIQUE constraints are used to enforce uniqueness on columns. However, a UNIQUE constraint can allow NULLs.
Tip
If you need to know the constraints already on a table, you can use the stored procedure
sp_helpconstraint table_name
to list all the constraint types, names, and columns for the table you specify.
CHECK Constraints
CHECK constraints can be used to restrict data depending on a "pass or fail" test of its requirements. It's similar to using a WHERE condition with the SELECT statement. The following are examples of the CHECK constraint.
Checking for a Phone Number Format
This example can be used to check a phone number to make sure that it fits the standard format of (xxx)xxx-xxxx:
ALTER TABLE customer_info ADD CONSTRAINT ck_phone CHECK (cell_phone LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
CHECK Constraint Using an Expression
In this example, assume that you have a job code (56) that is no longer being used and you don't want it added to the database. You could just put a CHECK constraint on the column:
ALTER TABLE employee ADD CONSTRAINT ck_job_code CHECK (job_code <> '56')