Lesson 2:Implementing Integrity Constraints

3 4

A constraint is a property assigned to a table or a column within a table that prevents invalid data values from being placed in the specified column(s). For example, a UNIQUE or PRIMARY KEY constraint prevents you from inserting a value that is a duplicate of an existing value; a CHECK constraint prevents you from inserting a value that does not match a search condition; and a FOREIGN KEY constraint enforces a link between data in two tables. Some documentation about constraints includes discussions about nullability and DEFAULT definitions, and in Transact-SQL, DEFAULT is indeed one type of constraint. However, because nullability and DEFAULT definitions are discussed in Chapter 4, "Implementing SQL Server Databases and Tables," this lesson focuses only on PRIMARY KEY constraints, UNIQUE constraints, FOREIGN KEY constraints, and CHECK constraints.

---

After this lesson, you will be able to:

• Describe the four main classes of constraints.
• Implement constraints in a SQL Server database.

Estimated lesson time: 35 minutes

---

Introduction to Integrity Constraints

Constraints enable you to define the way SQL Server 2000 automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanisms for enforcing integrity. Using constraints is preferred to using triggers, rules, or defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.

Constraints can be column constraints or table constraints:

• A column constraint is specified as part of a column definition and applies only to that column.
• A table constraint is declared independently from a column definition and can apply to more than one column in a table.

Table constraints must be used when more than one column is included in a constraint. For example, if a table has two or more columns in the primary key, you must use a table constraint to include both columns in the primary key. Consider a table that records events happening in a computer in a factory. Assume that events of several types can happen at the same time, but no two events happening at the same time can be of the same type. This rule can be enforced in the table by including both the type and time columns in a two-column primary key, as shown in the following CREATE TABLE statement:

 CREATE TABLE FactoryProcess     (     EventType INT,     EventTime DATETIME,     EventSite CHAR(50),     EventDesc CHAR(1024),     CONSTRAINT event_key PRIMARY KEY (EventType, EventTime)     ) 

SQL Server supports four main classes of constraints: PRIMARY KEY constraints, UNIQUE constraints, FOREIGN KEY constraints, and CHECK constraints.

PRIMARY KEY Constraints

A table usually has a column (or combination of columns) whose values uniquely identify each row in the table. This column (or columns) is called the primary key of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or alter a table.

A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints ensure unique data, they are often defined for identity columns. When you specify a PRIMARY KEY constraint for a table, SQL Server 2000 enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries.

If a PRIMARY KEY constraint is defined for more than one column, values can be duplicated within one column—but each combination of values from all of the columns in the PRIMARY KEY constraint definition must be unique. Figure 5.2 illustrates how the au_id and title_id columns in the TitleAuthor table form a composite PRIMARY KEY constraint, which ensures that the combination of au_id and title_id is unique.

Figure 5.2  The primary key of the TitleAuthor table in the Pubs database.

Creating PRIMARY KEY Constraints

You can create a PRIMARY KEY constraint by using one of the following
methods:

• Creating the constraint when the table is created (as part of the table defi-nition)
• Adding the constraint to an existing table, provided that no other PRIMARY KEY constraint already exists

You can modify or delete a PRIMARY CONSTRAINT once it has been created. For example, you might want the PRIMARY KEY constraint of the table to reference other columns, or you might want to change the column order, index name, clustered option, or fill factor of the PRIMARY KEY constraint. You cannot change the length of a column defined with a PRIMARY KEY constraint.

NOTE

---

To modify a PRIMARY KEY constraint by using Transact-SQL, you must first delete the existing PRIMARY KEY constraint and then re-create it with the new definition.

The following CREATE TABLE statement creates the Table1 table and defines the Col1 column as the primary key:

 CREATE TABLE Table1     (     Col1 INT PRIMARY KEY,     Col2 VARCHAR(30)     ) 

You can also define the same constraint by using a table-level PRIMARY KEY constraint:

 CREATE TABLE Table1     (     Col1 INT,     Col2 VARCHAR(30),     CONSTRAINT table_pk PRIMARY KEY (Col1)     ) 

You can use the ALTER TABLE statement to add a PRIMARY KEY constraint to an existing table:

 ALTER TABLE Table1 ADD CONSTRAINT table_pk PRIMARY KEY (Col1) 

When a PRIMARY KEY constraint is added to an existing column (or columns) in the table, SQL Server 2000 checks the existing data in the columns to ensure that it follows the rules for primary keys:

• No null values
• No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. You cannot add a PRIMARY KEY constraint that violates these rules.

SQL Server automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist in the table (or a non-clustered index is not explicitly specified), a unique, clustered index is created to enforce the PRIMARY KEY constraint.

IMPORTANT

---

A PRIMARY KEY constraint cannot be deleted if it is referenced by a FOREIGN KEY constraint in another table. The FOREIGN KEY constraint must be deleted first. FOREIGN KEY constraints are discussed later in this lesson.

UNIQUE Constraints

You can use UNIQUE constraints to ensure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, you should use a UNIQUE constraint instead of a PRIMARY KEY constraint in the following situations:

• If a column (or combination of columns) is not the primary key.  Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.
• If a column allows null values.  UNIQUE constraints can be defined for columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values.

A UNIQUE constraint can also be referenced by a FOREIGN KEY constraint.

Creating UNIQUE Constraints

You can create a UNIQUE constraint in the same way that you create a PRIMARY KEY constraint:

• By creating the constraint when the table is created (as part of the table definition)
• By adding the constraint to an existing table, provided that the column or combination of columns comprising the UNIQUE constraint contains only unique or NULL values. A table can contain multiple UNIQUE constraints.

You can use the same Transact-SQL statements to create a UNIQUE constraint that you used to create a PRIMARY KEY constraint. Simply replace the words PRIMARY KEY with the word UNIQUE. As with PRIMARY KEY constraints, a UNIQUE constraint can be modified or deleted once it has been created.

When a UNIQUE constraint is added to an existing column (or columns) in the table, SQL Server 2000 (by default) checks the existing data in the columns to ensure that all values, except null, are unique. If a UNIQUE constraint is added to a column that has duplicated values, SQL Server returns an error and does not add the constraint.

SQL Server automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an attempt is made to insert a duplicate row, SQL Server returns an error message saying that the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered index is explicitly specified, a unique, non-clustered index is created by default to enforce the UNIQUE constraint.

FOREIGN KEY Constraints

A foreign key is a column or combination of columns used to establish and enforce a link between the data in two tables. Create a link between two tables by adding a column (or columns) to one of the tables and defining those columns with a FOREIGN KEY constraint. The columns will hold the primary key values from the second table. A table can contain multiple FOREIGN KEY constraints.

For example, the Titles table in the Pubs database has a link to the Publishers table because there is a logical relationship between books and publishers. The pub_id column in the Titles table matches the primary key column in the Publishers table, as shown in Figure 5.3. The pub_id column in the Titles table is the foreign key to the Publishers table.

Figure 5.3  A FOREIGN KEY constraint defined in the Titles table of the Pubs database.

You can create a foreign key by defining a FOREIGN KEY constraint when you create or alter a table. In addition to a PRIMARY KEY constraint, a FOREIGN KEY constraint can reference the columns of a UNIQUE constraint in another table.

A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, then verification of the FOREIGN KEY constraint will be skipped.

NOTE

---

A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table (self-referencing tables).

Although the primary purpose of a FOREIGN KEY constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. For example, if the row for a publisher is deleted from the Publishers table and the publisher's ID is used for books in the Titles table, the relational integrity between the two tables is broken. The deleted publisher's books are orphaned in the titles table without a link to the data in the Publishers table. A FOREIGN KEY constraint prevents this situation. The constraint enforces referential integrity by ensuring that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail if the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table.

To successfully change or delete a row in a FOREIGN KEY constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table—thereby linking the foreign key to different primary key data.

Creating FOREIGN KEY Constraints

You can create a FOREIGN KEY constraint by using one of the following methods:

• Creating the constraint when the table is created (as part of the table defi-nition)
• Adding the constraint to an existing table, provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraint or a UNIQUE constraint in another (or the same) table

You can modify or delete a FOREIGN KEY constraint once it has been created. For example, you might want the table's FOREIGN KEY constraint to reference other columns. You cannot change the length of a column defined with a FOREIGN KEY constraint.

NOTE

---

To modify a FOREIGN KEY constraint by using Transact-SQL, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition.

The following CREATE TABLE statement creates the Table1 table and defines the Col2 column with a FOREIGN KEY constraint that references the EmployeeID column, which is the primary key in the Employees table:

 CREATE TABLE Table1     (     Col1 INT PRIMARY KEY,     Col2 INT REFERENCES Employees(EmployeeID)     ) 

You can also define the same constraint by using a table-level FOREIGN KEY constraint:

 CREATE TABLE Table1     (     Col1 INT PRIMARY KEY,     Col2 INT,     CONSTRAINT col2_fk FOREIGN KEY (Col2)     REFERENCES Employees (EmployeeID)     ) 

You can use the ALTER TABLE statement to add a FOREIGN KEY constraint to an existing table:

 ALTER TABLE Table1 ADD CONSTRAINT col2_fk FOREIGN KEY (Col2) REFERENCES Employees (EmployeeID) 

When a FOREIGN KEY constraint is added to an existing column (or columns) in the table, SQL Server 2000 (by default) checks the existing data in the columns to ensure that all values, except null values, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint. You can prevent SQL Server from checking the data in the column against the new constraint, however, and force it to add the new constraint regardless of the data in the column. This option is useful when the existing data already meets the new FOREIGN KEY constraint or when a business rule requires the constraint to be enforced only from this point forward.

You should be careful when adding a constraint without checking existing data, however, because this action bypasses the controls in SQL Server that enforce the data integrity of the table.

Disabling FOREIGN KEY Constraints

You can disable existing FOREIGN KEY constraints when performing the following actions:

• Executing INSERT and UPDATE statements.  Disable a FOREIGN KEY constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database. Disabling the constraint enables data in the table to be modified without being validated by the constraints.
• Implementing replication processing.  Disable a FOREIGN KEY constraint during replication if the constraint is specific to the source database. When a table is replicated, the table definition and data are copied from the source database to a destination database. These two databases are usually (but not necessarily) on separate servers. If the FOREIGN KEY constraints are specific to the source database but are not disabled during replication, they might unnecessarily prevent new data from being entered in the destination database.

CHECK Constraints

CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are placed in a column. The difference is in how they determine which values are valid. FOREIGN KEY constraints get the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, it is possible to limit the range of values for a salary column by creating a CHECK constraint that allows only data ranging from $15,000 through $100,000. This feature prevents the entering of salaries from outside the normal salary range.

You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. To allow only data that ranges from $15,000 through $100,000, the logical expression is as follows:

 salary >= 15000 AND salary <= 100000 

You can apply multiple CHECK constraints to a single column. The constraints are evaluated in the order in which they are created. In addition, you can apply a single CHECK constraint to multiple columns by creating it at the table level. For example, a multiple-column CHECK constraint can be used to confirm that any row with a country column value of USA also has a two-character value in the state column. This feature enables multiple conditions to be checked in one place.

Creating CHECK Constraints

You can create a CHECK constraint by using one of the following methods:

• Creating the constraint when the table is created (as part of the table definition)
• Adding the constraint to an existing table

You can modify or delete CHECK constraints once they have been created. For example, you can modify the expression used by the CHECK constraint on a column in the table.

NOTE

---

To modify a CHECK constraint using Transact-SQL, you must first delete the existing CHECK constraint and then re-create it with the new definition.

The following CREATE TABLE statement creates the Table1 table and defines the Col2 column with a CHECK constraint that limits the column-entered values to a range between 0 and 1000:

 CREATE TABLE Table1     (     Col1 INT PRIMARY KEY,     Col2 INT     CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000),     Col3 VARCHAR(30)     ) 

You can also define the same constraint by using a table-level CHECK constraint:

 CREATE TABLE Table1     (     Col1 INT PRIMARY KEY,     Col2 INT,     Col3 VARCHAR(30),     CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000)     ) 

You can use the ALTER TABLE statement to add a CHECK constraint to an existing table:

 ALTER TABLE Table1 ADD CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000) 

When a CHECK constraint is added to an existing table, the CHECK constraint can apply either to new data only or to existing data as well. By default, the CHECK constraint applies to existing data as well as to any new data. The option of applying the constraint to new data only is useful when the existing data already meets the new CHECK constraint or when a business rule requires the constraint to be enforced only from this point forward.

For example, an old constraint might require postal codes to be limited to five digits, but a new constraint might require nine-digit postal codes. Old data with five-digit postal codes is still valid and will coexist with new data that contains nine-digit postal codes. Therefore, only new data should be checked against the new constraint.

You should be careful when adding a constraint without checking existing data, however, because this action bypasses the controls in SQL Server 2000 that enforce the integrity rules for the table.

Disabling CHECK Constraints

You can disable existing CHECK constraints when performing the following actions:

• Executing INSERT and UPDATE statements.  Disable a CHECK constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database. Disabling the constraint allows data in the table to be modified without being validated by the constraints.
• Implementing replication processing.  Disable a CHECK constraint during replication if the constraint is specific to the source database. When a table is replicated, the table definition and data are copied from the source database to a destination database. These two databases are usually (but not necessarily) on separate servers. If the CHECK constraints specific to the source database are not disabled, they might unnecessarily prevent new data from being entered into the destination database.

Exercise 2:  Adding Constraints to Existing Tables

In this exercise, you will add constraints to the tables that you created in the BookShopDB database. You will need to refer to the data model that you developed and to the business rules that you identified in Exercise 3 of Chapter 3, "Designing a SQL Server Database." Note that every table should have a primary key. In some cases, this primary key will be made up of two columns. In addition, several tables will require foreign keys, and the Customers table will require a CHECK constraint. The business rules and data model will help you determine when to define constraints. For example, one of the business rules concerning the Books table is that the ConditionID column must contain a value that is listed in the ConditionID column of the BookCondition table. This rule tells you that you should define a FOREIGN KEY constraint for the ConditionID column of the Books table that references the ConditionID column of the BookCondition table. To perform this exercise, you should be logged into your Windows 2000 Server computer as Administrator.

To add a PRIMARY KEY constraint to the Authors table

1. Refer to the business rules and database design and identify which column (or columns) in the Authors table should be defined with a PRIMARY KEY constraint.

At this point in the database development process, it should be fairly obvious which column should be configured as the primary key. Remember that a PRIMARY KEY constraint is defined for a column (or columns) whose values uniquely identify each row in the table.

Which column (or columns) in the Authors table should be defined with a PRIMARY KEY constraint?

2. Open SQL Query Analyzer and connect to your local server.
3. In the Editor pane of the Query window, enter the following Transact-SQL code:

 USE BookShopDB ALTER TABLE Authors ADD CONSTRAINT authors_pk PRIMARY KEY (AuthorID) 

In this statement, you are adding a PRIMARY KEY constraint (authors_pk) to the AuthorID column of the Authors table in the BookShopDB Database.

4. Execute the Transact-SQL statement.

A message is displayed in the Messages tab of the Results pane, stating that the command has completed successfully.

To add a PRIMARY KEY constraint to the BookAuthors table

1. Refer to the business rules and database design and identify which column (or columns) in the BookAuthors table should be defined with a PRIMARY KEY constraint.

Which column (or columns) in the BookAuthors table should be defined with a PRIMARY KEY constraint?

2. In the Editor pane of the Query window, enter the following Transact-SQL code:

 ALTER TABLE BookAuthors ADD CONSTRAINT bookauthors_pk PRIMARY KEY (AuthorID, TitleID) 

In this statement, you are adding a PRIMARY KEY constraint (bookauthors_pk) to a combination of columns: the AuthorID column and the TitleID column.

3. Execute the Transact-SQL statement.

A message is displayed in the Messages tab of the Results pane, stating that the command has completed successfully.

To add a PRIMARY KEY constraint to the remaining tables in the BookShopDB database

1. Refer to the business rules and database design and identify which column (or columns) in the remaining tables should be defined with a PRIMARY KEY constraint.
2. Use SQL Query Analyzer to add a primary key to each remaining table in the BookShopDB database.

For the BookOrders table, the primary key should be created for the two columns in that table. For the other tables, use one identifier column for the primary key.

What Transact-SQL statements should you use to add the PRIMARY KEY constraints to the remaining tables?

Each table in the BookShopDB database should now be defined with a
PRIMARY KEY constraint.

To add FOREIGN KEY constraints to the BookAuthors table

1. Refer to the business rules and database design and identify which column (or columns) in the BookAuthors table should be defined with FOREIGN KEY constraints.

Remember that a FOREIGN KEY constraint establishes and enforces a link between two tables. By looking at the business rules and the database design, you can determine what these links should be.

Which column (or columns) in the BookAuthors table should be defined with a PRIMARY KEY constraint?

2. In the Editor pane of the Query window, enter the following Transact-SQL code:

 ALTER TABLE BookAuthors ADD CONSTRAINT authorid_fk FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID) 

In this statement, you are adding a FOREIGN KEY constraint (authorid_fk) to the AuthorID column of the BookAuthors table. The constraint references the AuthorID column in the Authors table.

3. Execute the Transact-SQL statement.

A message is displayed in the Messages tab of the Results pane, stating that the command has completed successfully.

4. In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 ALTER TABLE BookAuthors ADD CONSTRAINT titleid_fk FOREIGN KEY (TitleID) REFERENCES Books (TitleID) 

A message is displayed in the Messages tab of the Results pane, stating that the command has completed successfully.

To add FOREIGN KEY constraints to the Books, BookOrders, Orders, and Employees tables

1. Refer to the business rules and database design and identify which column (or columns) in the Books, BookOrders, Orders, and Employees tables should be defined with PRIMARY KEY constraints.
2. Use SQL Query Analyzer to add foreign keys to the Books, BookOrders, Orders, and Employees tables.

For the BookOrders table, you should add a FOREIGN KEY constraint to each column. For the Orders table, you should add a FOREIGN KEY constraint to each of the four columns that reference other tables. For the Books table and the Employees table, you should add only one FOREIGN KEY constraint per table.

What Transact-SQL statements should you use to add the FOREIGN KEY constraints to the Books, BookOrders, Orders, and Employees tables?

The appropriate tables in the BookShopDB database should now be defined with FOREIGN KEY constraints.

To add a CHECK constraint to the Customers table

1. Refer to the business rules and database design and identify which column (or columns) in the Customers table should be defined with a CHECK constraint.

Remember that a CHECK constraint enforces domain integrity by limiting the values that a column will accept.

Which column (or columns) in the Customers table should be defined with a CHECK constraint?

2. In the Editor pane of the Query window, enter the following Transact-SQL code:

 ALTER TABLE Customer ADD CONSTRAINT checknames_ck CHECK (FirstName NOT LIKE 'unknown' OR LastName NOT LIKE 'unknown') 

In this statement, you are adding a CHECK constraint that ensures that either the FirstName column or the LastName column includes a value other than unknown, which is the DEFAULT definition for both columns. In other words, both columns in the same row cannot contain the value unknown.

3. Execute the Transact-SQL statement.

A message is displayed in the Messages tab of the Results pane, stating that the command has completed successfully.

To add CHECK constraints to the Authors table and the Books table

1. Refer to the business rules and database design and identify which columns in the Authors table and the Books table should be defined with CHECK constraints.

Which columns in the Authors table and the Books table should be defined with CHECK constraints?

2. In the Editor pane of the Query window, enter the following Transact-SQL code:

 USE BookShopDB ALTER TABLE Authors  ADD CONSTRAINT authors1_ck  CHECK (YearBorn LIKE ('[1-2][0,6-9][0-9][0-9]')       OR (YearBorn = 'N/A')) ALTER TABLE Authors  ADD CONSTRAINT authors2_ck  CHECK (YearBorn NOT LIKE '[1][0][0-9][0-9]') ALTER TABLE Authors  ADD  CONSTRAINT authors3_ck  CHECK (YearBorn NOT LIKE '[2][6-9][0-9][0-9]') ALTER TABLE Authors  ADD CONSTRAINT authors4_ck  CHECK (YearDied LIKE ('[1-2][0,6-9][0-9][0-9]')       OR (YearDied = 'N/A')) ALTER TABLE Authors  ADD  CONSTRAINT authors5_ck  CHECK (YearDied NOT LIKE '[1][0][0-9][0-9]') ALTER TABLE Authors  ADD  CONSTRAINT authors6_ck  CHECK (YearDied NOT LIKE '[2][6-9][0-9][0-9]') ALTER TABLE Books ADD CONSTRAINT books1_ck  CHECK (PubDate LIKE ('[1-2][0,6-9][0-9][0-9]')       OR (PubDate = 'N/A')) ALTER TABLE Books  ADD  CONSTRAINT books2_ck  CHECK (PubDate NOT LIKE '[1][0][0-9][0-9]') ALTER TABLE Books  ADD  CONSTRAINT books3_ck  CHECK (PubDate NOT LIKE '[2][6-9][0-9][0-9]') 

In this statement, you are adding CHECK constraints to the YearBorn column, the YearDied column, and the PubDate column to limit the years that can be added to these columns.

3. Execute the Transact-SQL statement.

A message is displayed in the Messages tab of the Results pane, stating that the command has completed successfully.

To verify that constraints have been added to the tables

1. In the Editor pane of the Query window, enter and execute the following Transact-SQL code:

 sp_helpconstraint Authors 

The Grids tab of the Results pane displays information about the Authors table.

2. Scroll through the Grids tab until you reach the section about constraints.

The query result should include all the constraints that you have created for this table, which includes one PRIMARY KEY constraint and four DEFAULT constraints.

3. Use the sp_helpconstraint system stored procedure to view constraint information in the remaining tables. Verify that all appropriate constraints have been created.
4. Close SQL Query Analyzer.

Lesson Summary

Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Constraints can be column constraints or table constraints. Table constraints must be used when more than one column is included in a constraint. SQL Server supports four main classes of constraints: PRIMARY KEY constraints, UNIQUE constraints, FOREIGN KEY constraints, and CHECK constraints. A PRIMARY KEY constraint is defined for a column or combination of columns to ensure that the values within those columns uniquely identify each row in the table. A UNIQUE constraint is similar to a PRIMARY KEY constraint. The UNIQUE constraint ensures that no duplicate values are entered in specific columns that do not participate in a primary key. A FOREIGN KEY constraint is a column or combination of columns used to establish and enforce a link between the data in two tables. CHECK constraints enforce domain integrity by limiting the values that a column will accept.