Working with SQL Server 2005 – SQL 2005 Tutorial – Learn SQL Part 3
Constraints are used to ensure validity of data in a table and consistency of data across tables.
You can define constraints at the column or table level. A constraint defined at the column-level is a part of the column definition and applies only on that particular column. A table-level constraint declaration is independent from a column definition and can be simultaneously applied to multiple columns in the table. For example, when you need to include multiple columns in the PRIMARY KEY, the PRIMARY KEY constraint has to be defined at the table level.
A UNIQUE constraint can be applied to a column or a combination of columns to ensure uniqueness of data values in these columns.
If a column is defined with the UNIQUE constraint, then duplicate values cannot be entered in that column. If the UNIQUE constraint is applied to a combination of columns, then the uniqueness is checked for the combination of values in these columns. That is, individual columns can have duplicate data values, but all the columns cannot simultaneously have the same values in multiple records.
UNIQUE constraint can be defined on a column or a combination of columns within the CREATE TABLE or ALTER TABLE statement.
Entity Integrity Through UNIQUE Constraint
The UNIQUE constraint ensures entity integrity in a table.
For example, consider the Account_Transactions table. The TransNumber column is defined with a UNIQUE constraint. Hence, in the Account_Transactions table,
- The values in the TransNumber column will be unique.
- The TransNumber column can have only one null value.
As the values in the TransNumber column will always be unique, no two records in the Account_Transactions table will have the exact same values in all the columns. Thus, entity integrity will be maintained.
CHECK constraint defines the range and format for the values entered in a column. You can create CHECK constraint with a logical expression that returns a TRUE or FALSE value to validate the data entered.
SQL Server 2005 allows you to define more than one CHECK constraint for a single column. In addition, you can apply a single CHECK constraint to a group of columns.
A CHECK constraint can be specified on a column within the CREATE TABLE or ALTER TABLE statement.
The purpose of a PRIMARY KEY column is to uniquely identify each record within a table. A PRIMARY KEY constraint checks for uniqueness of data in the PRIMARY KEY column and disallows duplicate values to be entered.
A PRIMARY KEY can be specified either on a single column or a combination of columns. If the PRIMARY KEY is specified on a combination of columns, then uniqueness is checked for the combination of the values in these columns.
A PRIMARY KEY constraint can be specified on a column or a combination of columns within the CREATE TABLE or ALTER TABLE statement.
Entity Integrity Through PRIMARY KEY
The PRIMARY KEY constraint ensures entity integrity in a table.
For example, in the Account_Transactions table of a bank database, if the PRIMARY KEY is defined on the combination of CustlD and Trans ID, then:
- There can be multiple transactions with the same CustlD.
- There can be multiple transactions with the same TranslD.
- There can never be multiple transactions with both the CustlD as well as the TranslD same.
The combination of values of CustlD and TranslD is always unique for all transactions. Hence, no two records will ever have the exact same values in all the columns, thus ensuring entity integrity.
FOREIGN KEY Constraint
A FOREIGN KEY constraint is used when you need the values of a column to be a subset of a pre-existing list of values. This pre-existing list needs to exist as part of a PRIMARY KEY column or a column defined by a UNIQUE constraint in another table. The column specified with the FOREIGN KEY constraint should have reference to the PRIMARY KEY or the UNIQUE column. The FOREIGN KEY column and the referred PRIMARY KEY or UNIQUE column should have similar data types.
A FOREIGN KEY constraint can be defined on a table within the CREATE TABLE or ALTER TABLE Statement.
Use of FOREIGN KEY Constraint
A FOREIGN KEY constraint creates logical relationships between multiple tables of a database. When inserting a record, the value supplied for the FOREIGN KEY column is compared to the values in the PRIMARY KEY column of the referenced table. If a match is found, this value is accepted and the record is inserted. If no match is found, the record will be discarded.
When records in the referenced PRIMARY KEY table are modified, changes can be cascaded to corresponding records in the referring FOREIGN KEY table. As changes in data are cascaded from the referenced table to the referring table, consistency of data is maintained across tables. Hence referential integrity is maintained. The cascading options are defined in the references clause of the CREATE TABLE or ALTER TABLE Statement.
SQL Server 2005 provides the following two cascading options:
- ON UPDATE CASCADE
When records are updated in tables with the PRIMARY KEY, referring records in tables with the FOREIGN KEY are updated using the on update cascade option.
- ON DELETE CASCADE
When records are deleted in tables with the PRIMARY KEY, referring records in tables with the FOREIGN KEY are deleted using the on delete cascade option.
Test Your Knowledge
Which of these statements about the integrity constraints of SQL Server are true and which statements are false?
(A) The PRIMARY KEY constraint allows a null value to be entered only once in a column.
(B) CHECK constraint defines the range and format for the values entered in a column.
(C) Constraints are used to ensure validity of data in a table and consistency of data across tables.
(D) A column specified with a UNIQUE constraint should have reference to the PRIMARY KEY column of another table.
(E) The FOREIGN KEY constraint helps in maintaining referential integrity in the database.