CONSTRAINT

Syntax:: CONSTRAINT constraint_name
{
    PRIMARY KEY
    | UNIQUE
    | NOT NULL
    | REFERENCES foreign_table
       [ ( foreign_field1, foreign_field2 ) ]
}

CONSTRAINT constraint_name
{
    PRIMARY KEY ( primary1 [ , primary2 [ , ... ] ] )
    | UNIQUE ( unique1 [ , unique2 [ , ... ] ] )
    | NOT NULL ( notnull1 [ , notnull2 [ , ... ] ] )
    | FOREIGN KEY ( ref1 [ , ref2 [ , ... ] ] )
    | REFERENCES foreign_table [ ( foreign_field1 [ , foreign_field2 [ , ... ] ] ) ]
}
constraint_name: Is the name of the constraint being created.
PRIMARY KEY: Is a parameter that identifies the column or set of columns whose values uniquely identify each row in a table. Each table can only have one primary key constraint.
UNIQUE: Is a constraint that enforces the uniqueness of the values in a set of columns.
NOT NULL: Is a parameter that indicates whether a column can or cannot contain null values.
REFERENCES: Is a keyword that indicates a relationship between two tables is being established..
foreign_table: Is the name of the table that the relationship is to be made with.
foreign_field1: Is a parameter that lists the name of the field(s) from the foreign_table on which to create the foreign key.
primary1: Is a parameter that specifies a list of fields that are to be used as the primary key.
unique1: Is a parameter that specifies a list of fields that are to be unique.
notnull1: Is a parameter that specifies a list of fields that cannot have null values.
ref1: Is a parameter that specifies a list of fields on which a foreign key is to be created.

The CONSTRAINT clause is used to maintain data integrity by providing limits on the values that can be inserted into a column or table.

While a CONSTRAINT clause is somewhat similar to an INDEX, a CONSTRAINT can establish a relationship with another table. To place a constraint on a single field in a CREATE TABLE or ALTER TABLE statement, follow the definition of that field with a CONSTRAINT clause. This consists of a name for the constraint and one of the following reserved words: PRIMARY KEY, UNIQUE, NOT NULL or REFERENCES.

Examples

Code:


				CREATE TABLE Names (NameID INTEGER CONSTRAINT NameIDKey PRIMARY KEY, 

				FirstName VARCHAR (20), LastName VARCHAR (20), DateOfBirth DATETIME);

Output:


				The command(s) completed successfully.

Explanation:

The PRIMARY KEY reserved word designates a field (or set of fields) as a primary key. It is mandatory that all values in the primary key must be unique and not NULL. An error will occur if you try to use a PRIMARY KEY constraint on a table that already has a primary key. This example sets the NameID field to be the primary key of the Names table.

Language(s): MS SQL Server

Code:


				CREATE TABLE Names (NameID INTEGER, FirstName VARCHAR (20) CONSTRAINT 

				UniqueName UNIQUE, LastName VARCHAR (20), DateOfBirth DATETIME);

Output:


				The command(s) completed successfully.

Explanation:

The reserved word UNIQUE requires that the value entered into the specified field (or combination of fields) be unique, as in this example which only allows unique first names.

Language(s): MS SQL Server

Code:


				CREATE TABLE Names (NameID INTEGER, FirstName VARCHAR (20) NOT NULL, 

				LastName VARCHAR (20) NOT NULL, DateOfBirth DATETIME NOT NULL);

Output:


				The command(s) completed successfully.

Explanation:

This statement shows how the reserved word NOT NULL can be used to specify that a field in a table must always contain valid data (and cannot contain NULL).

Language(s): MS SQL Server

Code:


				CREATE TABLE Sales (SalesID INTEGER, ProductID INTEGER, Item TEXT CONSTRAINT 

				ForeignRefs REFERENCES Products (Item));

Output:


				The command(s) completed successfully.

Explanation:

This example demonstrates how relationships can be established with a field in a foreign table (as long as it only contains unique values) by using the reserved word REFERENCES and naming the foreign table and field.

Language(s): MS SQL Server

Code:


				CREATE TABLE Sales (SalesID INTEGER, ProductID INTEGER CONSTRAINT 

				ForeignKeyRef REFERENCES Products, Item TEXT);

Output:


				The command(s) completed successfully.

Explanation:

If the field in the foreign table is the primary key, you only need name the table and the database engine references it by default.

Language(s): MS SQL Server

Code:


				CREATE TABLE Names (NameID INTEGER, FirstName VARCHAR (20), 

				LastName VARCHAR (20), DateOfBirth DATETIME, 

				CONSTRAINT NameKey PRIMARY KEY(FirstName, LastName));

Output:


				The command(s) completed successfully.

Explanation:

When you want to apply a constraint to more than one field, a multiple-field constraint, you can do so by adding the CONSTRAINT clause after all the field definitions. The preceding example makes the two fields FirstName and LastName a joint primary key.

Language(s): MS SQL Server

Code:


				CREATE TABLE Names (NameID INTEGER, FirstName VARCHAR (20), 

				LastName VARCHAR (20), DateOfBirth DATETIME, 

				CONSTRAINT UniqueFields UNIQUE(FirstName, LastName, DateOfBirth));

Output:


				The command(s) completed successfully.

Explanation:

This example requires the combination of FirstName, LastName and DateOfBirth to be unique.

Note: It is acceptable for one or more of the fields in a multiple-field constraint to contain values that are the same, as long as the combination of values in all the constrained fields is unique.

Language(s): MS SQL Server

Code:


				CREATE TABLE Cartoons (CartoonID INTEGER, ShowName TEXT, 
 
				ToonName VARCHAR (15), ToonType VARCHAR (15), 
 
				CONSTRAINT ReferForeignField FOREIGN KEY(ToonName, ToonType) 

				REFERENCES Toons (CharacterName, ToonType));

Output:


				The command(s) completed successfully.

Explanation:

If you want to include a FOREIGN KEY that consists of more than one field, you must use a multiple-field constraint definition. The constraint definition must list the names of the referencing fields, the foreign table, and the referenced fields in the foreign table. The order of the referenced fields must correspond to the order of the referring fields.

Primary links

T-SQL » Clauses » CONSTRAINT

Examples

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

See Also: