So just how do you add a check constraint to a table or column? As with just about everything in SQL Server you can use SQL statements or Enterprise Manager to make the modifications. I’m going to show you how to add a constraint using Enterprise Manager, but feel free to look at the CREATE TABLE and ALTER TABLE syntax in Books Online for more information.
What we are going to do is add a check constraint similar to the country/state one above to the Customers table of the Northwind database. Start out by opening Enterprise Manager, select your server, expand the Databases folder, expand the Northwind database, and select tables. Next right click the Customers table and select Design Table. The Design Table window should appear.
Next click the Manage Constraints icon on the toolbar.
Once the Properties window appears click the New button and enter the following statement for the Constraint expression (again, this is just a shortened list):
Country <> ‘USA’ OR Region IN (‘AL’, ‘AK’, ‘CA’, ‘FL’, ‘GA’, ‘ID’, ‘MT’, ‘NM’, ‘OR’, ‘WA’, ‘WY’)
Last, for Constraint name, enter CK_Customers_USA_Region. Your screen should look like this.
Before clicking close lets look at what the three options at the bottom do:
- Check existing data on creation will check all existing data in the table to see if any rows violate the check constraint. Note that the check is not performed until the table is saved and if there are any invalid rows an error is shown and the table modifications are not saved. You can leave this unchecked to only apply this constraint to new/updated rows.
- Enforce constraint for replication will enforce this constraint if the table is replicated to another database. We will be getting into replication later in the series.
- Enforce constraint for INSERT and UPDATE will enforce the constraint for any INSERT and UPDATE operations to the table.
Close the Properties window and save the table. If you receive the error Unable to add constraint ‘CK_Customers_USA_Region’, you probably have rows that were not part of the original Northwind database that have the Country USA and a Region abbreviation that is not part of the check constraint we created.
One last thing to note about creating check constraints in Enterprise Manager is that the only place you add constraints is on the Check Constraints tab of the tables properties window (see above). In other words there is no way to specify that a check constraint should be tied to a column in Enterprise Manager whereas with the CREATE TABLE statement you can add a constraint to a specific column. However, the good news is that Enterprise Manager will automatically decide if it should add a column or table level check constraint based on the constraint you enter.