Constraints are limitations placed on a column or a table. There are two types of constraints:
- column-level contraints that control the valid values that are placed in a column
- table-level contraints that control a column value compared to other rows in the table
A column-level constraint pertains to a single column in a table, and allows or prohibits values based on a rule. For example, perhaps a gender column should only be allowed to contain a value of F or M.
A table-level constraint applies to multiple rows in a table. This type of constraint if often used when a rule involves multiple columns, such as allowing only distinct values. A UNIQUE constraint is one type of table-level constraint. An example would be if DepartmentName cannot be duplicated in the Departments table.
Another example of a table-level constraint would be if a value for one column depends on tthe value in another column. For example, if a StateCode column needs to be required - but only if the CountryCode column value is 'USA'.
Adding a UNIQUE Constraint to a Column Definition
Some common column-level constraints have already been discussed in this material. Both DEFAULT and NOT NULL are constraints. As part of the course design standards, they have normally been assigned as part of a column definition.
Constraints can be included as part of a column definition in the CREATE TABLE statement, or they can be added later using the ALTER TABLE statement. When included as part of a column definition, DEFAULT and NOT NULL do not require a full CONSTRAINT clause. The other common constraints are UNIQUE and CHECK, and they both require an additional CONSTRAINT clause.
A UNIQUE constraint forces the value for a column to be distinct for all rows in that table. This is a table-level constraint. As part of a column definition, the syntax of that command looks like the following:
|keyword||constraint name||type keyword||rule|
title varchar(40) not null CONSTRAINT UX_articles_title UNIQUE (title)
Notice that the title column has multiple constraints. First, it is a required column, due to the NOT NULL keywords. Because there is no DEFAULT, a value for title must be provided when inserting or updating a record.
Second, a UNIQUE constraint has been defined, meaning the value provided for title must be distinct - it cannot already exist elsewhere in the table. This is true both when an INSERT statement is used to add a row and if an UPDATE statement is used to change the value. In the latter case, the new UPDATE value will be checked to make sure it does not already exist in another record.
The Result of a Constraint Violation
When a constraint is violated, the database engine generates a run-time error, and the entire INSERT or UPDATE operation is rejected. In the case of an INSERT, no new record will be added to the table. If there is an IDENTITY column in the table, such as the primary key, the next value for that table will be discarded. For example, assume there is a UNIQUE constraint on the DepartmentName column in dbo.Departments, and the last record contains a DepartmentID of 2102. If the application attempts to add a record for a second 'John Smith', the INSERT operation will be rejected with an error. The next value for DepartmentID of 2103 will be discarded (never to be used again) and the next successful INSERT will be assigned the value of 2104
A Side Note About Values
It may be helpful to take a minute and discuss varchar and char values. The standard configuration of SQL Server is that columns are case-insensitive. This means that "A" is effectively the same as "a" for operations. "PAUL" and "Paul" will display differently, but will be considered the same thing when compared, for example. If a column has a UNIQUE constraint for a name field, for instance, the name "PAUL" could not be added for a row if "Paul" already existed in another row. That said, the values "PAUL" and "PAUL " are NOT the same. The latter value has an extra space; it has five characters compared to the former value that has 4 characters.
What this all means is that constraints for string columns can be tricky. If the application responsible for providing the data is not consistent in terms or removing unwanted spaces, for example, any CONSTRAINT statements may need to be more complex.
Another Side Note: Who is Responsible for Constraints?
While it is possible to create very specific constraints about how data should exist in the database, this type of control should not always be done solely in the database. The above scenario is a good example. If a name is expected to be unique, it makes sense to create a column-constraint in the database. But making sure the value to be entered does not have beginning or trailing spaces is better handled by the front-end application. It is more difficult for the application to check the table for a unique name (for several reasons), but it is easy on the front end to make sure that unwanted spaces are prevented or removed.
It is also the case that database constraints should be things that do not change very often. Where application requirements for some fields might change more often. So, decisions about database constraints are often tied to the overall application and data process - not just how the database should be configured on its own.
Another consideration is that sometimes data flows from multiple sources, not all of which can be controlled. For example, in addition to a data entry application, perhaps there is a secondary Internet site that pushes new records to the database. If the data from that source does not strip unwanted spaces from names, for example, then it may be necessary to create a more complex constraint that handles unwanted spaces, etc.
Adding a CHECK Constraint to a Column Definition
The purpose of a CHECK constraint is to limit what can be entered OR not entered into a column. This is a column-level constraint.
A CHECK constraint checks the value in an INSERT or UPDATE column to make sure it complies with the contraint rule. As part of a column definition, the syntax of the command looks like this:
|keyword||constraint name||type keyword||rule|
|CONSTRAINT||CK_table_column||CHECK||(columnname > x)|
Similar to the UNIQUE constraint, the CHECK constraint starts with the keyword CONSTRAINT. The keyword is followed by a constraint name. Many organizations will have standards that specify how constraints should be named. A prefix is not required, but helps identity the type of constraint. Common prefixes for constraints are often:
- DF - DEFAULT
- CK - CHECK
- UX - UNIQUE
- FK - FOREIGN KEY
It is also helpful to include both the table name and column name as part of the constraint name, since multiple tables may have the columns of the same name. In other words, CK_AMOUNT is a bad contraint name, since multiple tables might have an Amount column.
The rule for a CHECK constraint can include almost any type of comparison statement. It may use any of the comparison operators (=, !=, <>...), including IN, BETWEEN, NOT IN, etc. The following would all be valid.
age int not null CONSTRAINT CK_workapps_age CHECK (age >= 18 and < 66)
countrycode char(3) not null CONSTRAINT CK_popdata_countrycode CHECK (countrycode in ('USA','CAN','MEX'))
salary money not null CONSTRAINT CK_loanapp_salary CHECK (salary BETWEEN 25000 and 110000)
grade varchar(20) not null CONSTRAINT CK_scholarapps_grade CHECK (grade NOT in ('freshman','sophomore'))
A table-level constraint is one that applies to and must evaulate multiple records or columns.
Technically, the UNIQUE constraint is a table-level constraint. It must search all other records in the table to determine if a new value can be accepted. The UNIQUE constraint was explained above because it is similar to other column-level constraints, because it can be define using an inline clause as part of a column definition.
When a constraint must reference more than one column, it CANNOT be included as part of a single column definition. It can still be included as part of a table definition, but must be separated from the columns (using a comma), and is placed after the list of column definitions. Here is the syntax and an example:
|,keyword||constraint name||type keyword||rules|
,CONSTRAINT CK_locations_statecode CHECK (LEN(statecode)>0 OR country!='USA')
The above example has two rules: 1) a statecode must be provided (the len of the value must be 1 or greater) OR 2) the country must not be 'USA'. Since the rules include "OR", the entire CHECK clause is TRUE if either one of the rules is TRUE. In other words, if the countrycode is not equal to 'USA', things are okay. Things are also okay if the statecode is provided (regardless of country). Where the rules are violated is if countrycode='USA' AND statecode is blank.
This is not extremely complex, but it is more complex that most column-level CHECK constraint rules. And it might take some deeper thought - and some good testing - to make sure the constraint works as intended.
Foreign Key Constraints
A Foreign Key (FK) constraint is similar to a CHECK constraint. The difference is that the
check is always made against another table. In most cases, an FK is checked against the primary key
of another table. The syntax of an FK constraint looks like this:
|keyword||constraint name||type keyword||(FK Column)||keyword||PK Table (Column)|
|CONSTRAINT||FK_table_column||FOREIGN KEY||(FK column)||REFERENCES||Schema.Table (Column)|
storeid int null CONSTRAINT FK_managers_storeid FOREIGN KEY (storeid) REFERENCES xyz.stores (storeid)
Using constraints is a valued technique to assist with data integrity for a database. Constaints are often a backstop to prevent data corruption if there is a value in the UI or business layer of an application