SQL CONSTRAINT clauses: FOREIGN KEY, CHECK and DEFAULT

In SQL Server, SQL CONSTRAINT clauses let you control values entered into a column. Learn about FOREIGN KEY, CHECK and DEFAULT constraints here.

This is the second of two parts on various SQL CONSTRAINT clauses in SQL Server. This one covers the FOREIGN KEY, CHECK and DEFAULT constraints, while the first part explains PRIMARY KEY and UNIQUE constraints.

Robert Sheldon

A FOREIGN KEY SQL constraint links one or more columns in one table to corresponding columns in another table, which is called the referenced table. A foreign key column can contain only data that is also contained in the referenced column. For example, suppose we created our SpecialtyProducts table in the AdventureWorks database. We could then create a FOREIGN KEY constraint on the StoreID column that references the BusinessEntityID column in the Sales.Store table. As a result, the StoreID column would be able to contain only values that exist in the BusinessEntityID column.

A foreign key also enforces referential integrity, a type of data integrity that exists between related tables. However, a discussion of referential integrity is beyond the scope of this article. Read here for more information.

For the most part, foreign keys can only reference columns on which a PRIMARY KEY or UNIQUE constraint has been defined. However, they can also reference columns that are part of a unique index. In either case, referenced columns can be in the same table or in a different table, as long as those columns are in the same database. In addition, the foreign key columns must match the referenced columns in terms of number and data types.

Defining a foreign key on a table is similar to defining a primary key. The big difference is that you must include the referenced columns in the CONSTRAINT clause, as shown in the following example:

CREATE TABLE SpecialtyProducts

(

  StoreID INT NOT NULL

    REFERENCES Sales.Store (BusinessEntityID),

  ProductID INT NOT NULL,

  AltID CHAR(7) NOT NULL,

  ProductName NVARCHAR(50) NOT NULL,

  CONSTRAINT pk_StoreID_ProductID

    PRIMARY KEY CLUSTERED (StoreID, ProductID),

  CONSTRAINT uq_AltID

    UNIQUE NONCLUSTERED (AltID)

);

As you can see, the StoreID column definition includes the FOREIGN KEY constraint clause. However, the only required elements are the REFERENCES keyword, the referenced table (Sales.Store) and the referenced column (BusinessEntityID). As with other column constraints, the database engine assumes the CONSTRAINT keyword and generates the constraint name.

You can create a foreign key on the StoreID column to reference the BusinessEntityID column because the data types on the two columns are compatible (both INT). Plus, in this case, you're creating the foreign key on one column and referencing only one column, so the number of columns matches as well. Once you've created your foreign key on the StoreID column, it can contain only values that are in the BusinessEntityID column.

Not surprisingly, you can instead create a table constraint to define your foreign key:

CREATE TABLE SpecialtyProducts

(

  StoreID INT NOT NULL,

  ProductID INT NOT NULL,

  AltID CHAR(7) NOT NULL,

  ProductName NVARCHAR(50) NOT NULL,

  CONSTRAINT pk_StoreID_ProductID

    PRIMARY KEY CLUSTERED (StoreID, ProductID),

  CONSTRAINT uq_AltID

    UNIQUE NONCLUSTERED (AltID),

  CONSTRAINT fk_StoreID

    FOREIGN KEY (StoreID) REFERENCES Sales.Store (BusinessEntityID)

);

Notice that you must specify the foreign key column (or columns) after the FOREIGN KEY keywords. But the REFERENCES subclause is the same as that in a column constraint.

The CHECK SQL constraint

The CHECK SQL constraint controls the values that you can insert into the column on which the constraint is defined. The constraint definition contains one or more logical (Boolean) expressions that evaluate to be True or False. The value inserted into the column is verified against those expressions. If the expression returns True, the value is inserted; otherwise, it violates the constraint.

If you include more than one logical expression, you must use a logical operator such as AND or OR to link the expressions together and return a single value of True or False. However, you can define multiple CHECK constraints on a column. The constraints are processed in the order in which they were defined.

As with other SQL constraint types, you can create a CHECK constraint as part of a column definition, without including the CONSTRAINT keyword and constraint name, as shown in the following example:

CREATE TABLE SpecialtyProducts

(

  StoreID INT NOT NULL,

  ProductID INT NOT NULL,

  AltID CHAR(7) NOT NULL

    CHECK (AltID LIKE 'SP[1-9][0-9][0-9][0-9][0-9]'),

  ProductName NVARCHAR(50) NOT NULL,

  CONSTRAINT pk_StoreID_ProductID

    PRIMARY KEY CLUSTERED (StoreID, ProductID),

  CONSTRAINT uq_AltID

    UNIQUE NONCLUSTERED (AltID),

  CONSTRAINT fk_StoreID

    FOREIGN KEY (StoreID) REFERENCES Sales.Store (BusinessEntityID)

);

To create the constraint, you specify the CHECK keyword and then a logical expression. In this example, the expression specifies that the AltID value must match a specific string value. In this case, the string value defines a regular expression that starts with "SP" and ends with five digits. The first digit must be 1 through 9 and each remaining digit must be 0 through 9. Once you've defined your CHECK constraint, only values that evaluate to True can be inserted into the AltID column. For example, you can insert SP27930, but not SC01234.

As we've seen with other constraints, we can instead create a table constraint to achieve the same results:

CREATE TABLE SpecialtyProducts

(

  StoreID INT NOT NULL,

  ProductID INT NOT NULL,

  AltID CHAR(7) NOT NULL,

  ProductName NVARCHAR(50) NOT NULL,

  CONSTRAINT pk_StoreID_ProductID

    PRIMARY KEY CLUSTERED (StoreID, ProductID),

  CONSTRAINT uq_AltID

    UNIQUE NONCLUSTERED (AltID),

  CONSTRAINT fk_StoreID

    FOREIGN KEY (StoreID) REFERENCES Sales.Store (BusinessEntityID),

  CONSTRAINT ck_AltID

    CHECK (AltID LIKE 'SP[1-9][0-9][0-9][0-9][0-9]')

);

The table constraint starts with the CONSTRAINT keyword and constraint name, followed by the CHECK subclause, which is the same as the subclause used in the column constraint in the preceding example.

The DEFAULT SQL constraint

At times, you might want to automatically insert a value into a column when no other value is provided. That's where the DEFAULT SQL constraint comes in. You can use the constraint to define a column's default value. This can be a handy way to add the current date and time to a column or to avoid having to use NULL values.

You can define only one DEFAULT constraint per column, and the column cannot be configured with the TIMESTAMP data type or the IDENTITY property. If the default value is a string, it must be enclosed in single quotes. In addition, you can define a DEFAULT constraint only as a column constraint, not as a table constraint. For example, the following CREATE TABLE statement defines a DEFAULT constraint on the DateAdded column:

CREATE TABLE SpecialtyProducts

(

  StoreID INT NOT NULL,

  ProductID INT NOT NULL,

  AltID CHAR(7) NOT NULL,

  ProductName NVARCHAR(50) NOT NULL,

  DateAdded DATETIME NOT NULL DEFAULT (getdate()),

  CONSTRAINT pk_StoreID_ProductID

    PRIMARY KEY CLUSTERED (StoreID, ProductID),

  CONSTRAINT uq_AltID

    UNIQUE NONCLUSTERED (AltID),

  CONSTRAINT fk_StoreID

    FOREIGN KEY (StoreID) REFERENCES Sales.Store (BusinessEntityID),

  CONSTRAINT ck_AltID

    CHECK (AltID LIKE 'SP[1-9][0-9][0-9][0-9][0-9]')

);

The DEFAULT constraint definition starts with the DEFAULT keyword, followed by an expression that defines the default value. In this case, that expression is simply the getdate() function, which returns the current date and time.

If you want to name your DEFAULT constraint, you must also include the CONSTRAINT keyword and the constraint name as part of the column constraint definition:

CREATE TABLE SpecialtyProducts

(

  StoreID INT NOT NULL,

  ProductID INT NOT NULL,

  AltID CHAR(7) NOT NULL,

  ProductName NVARCHAR(50) NOT NULL,

  DateAdded DATETIME NOT NULL

    CONSTRAINT df_DateAdded DEFAULT (getdate()),

  CONSTRAINT pk_StoreID_ProductID

    PRIMARY KEY CLUSTERED (StoreID, ProductID),

  CONSTRAINT uq_AltID

    UNIQUE NONCLUSTERED (AltID),

  CONSTRAINT fk_StoreID

    FOREIGN KEY (StoreID) REFERENCES Sales.Store (BusinessEntityID),

  CONSTRAINT ck_AltID

    CHECK (AltID LIKE 'SP[1-9][0-9][0-9][0-9][0-9]')

);

Once the DEFAULT constraint has been added to the table, the current date and time will be automatically inserted into the DateAdded column whenever a row is inserted, unless a value is specified for that column.

Working with SQL Server constraints

The CONSTRAINT clause in SQL Server is a powerful tool for maintaining the integrity of your data. You can use the clause to create a number of different types of constraints, such as a PRIMARY KEY constraint, which ensures that each row in a table is unique, or a UNIQUE constraint, which ensures that the values in a column or set of columns are unique. To link tables together and establish referential integrity, you can define a FOREIGN KEY constraint on one or more columns, or you can use CHECK constraints to limit the values that can be inserted into a column. You can also create DEFAULT constraints on your columns to provide default values when adding rows to a table. Note, however, that what we've covered here provides only an introduction to constraints. Be sure to check out SQL Server Books Online for more details on each type as well as information about other ways to ensure the integrity of your data.

<< PRIMARY KEY and UNIQUE constraints

About the author:
is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.

This was first published in October 2013

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close