FOREIGN KEY constraints
statementsThe index generated by a PRIMARY KEY constraint can’t cause the number of indexes on
the table to exceed 999 nonclustered indexes and 1 clustered index.
If CLUSTERED or NONCLUSTERED isn’t specified for a PRIMARY KEY constraint,
CLUSTERED is used if there are no clustered indexes specified for UNIQUE constraints.
All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If
nullability isn’t specified, all columns participating in a PRIMARY KEY constraint have their
nullability set to NOT NULL.
If a primary key is defined on a CLR user-defined type column, the implementation of the
type must support binary ordering. For more information, see
CLR user-defined types.
If CLUSTERED or NONCLUSTERED isn’t specified for a UNIQUE constraint,
NONCLUSTERED is used by default.
Each UNIQUE constraint generates an index. The number of UNIQUE constraints can’t
cause the number of indexes on the table to exceed 999 nonclustered indexes and 1
clustered index.
If a unique constraint is defined on a CLR user-defined type column, the implementation
of the type must support binary or operator-based ordering. For more information, see
CLR user-defined types.
When a value other than NULL is entered into the column of a FOREIGN KEY constraint,
the value must exist in the referenced column; otherwise, a foreign key violation error
message is returned.
FOREIGN KEY constraints are applied to the preceding column, unless source columns are
specified.
FOREIGN KEY constraints can reference only tables within the same database on the same
server. Cross-database referential integrity must be implemented through triggers. For
more information, see
CREATE TRIGGER.
７
Note
For memory-optimized tables, the nullable key column is allowed.
FOREIGN KEY constraints can reference another column in the same table. This is referred
to as a self-reference.
The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one
reference column. This column must have the same data type as the column on which the
constraint is defined.
The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same
number of reference columns as the number of columns in the constraint column list. The
data type of each reference column must also be the same as the corresponding column
in the column list. The reference columns must be specified in the same order that was
used when specifying the columns of the primary key or unique constraint on the
referenced table.
CASCADE, SET NULL or SET DEFAULT can’t be specified if a column of type
is
part of either the foreign key or the referenced key.
CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined on tables that have
referential relationships with each other. If the Database Engine encounters NO ACTION,
it stops and rolls back related CASCADE, SET NULL and SET DEFAULT actions. When a
DELETE statement causes a combination of CASCADE, SET NULL, SET DEFAULT and NO
ACTION actions, all the CASCADE, SET NULL and SET DEFAULT actions are applied before
the Database Engine checks for any NO ACTION.
The Database Engine doesn’t have a predefined limit on either the number of FOREIGN
KEY constraints a table can contain that reference other tables, or the number of
FOREIGN KEY constraints that are owned by other tables that reference a specific table.
Nevertheless, the actual number of FOREIGN KEY constraints that can be used is limited
by the hardware configuration and by the design of the database and application. We
recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it
be referenced by no more than 253 FOREIGN KEY constraints. The effective limit for you
might be more or less depending on the application and hardware. Consider the cost of
enforcing FOREIGN KEY constraints when you design your database and applications.
FOREIGN KEY constraints aren’t enforced on temporary tables.
FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE
constraints in the referenced table or in a UNIQUE INDEX on the referenced table.
If a foreign key is defined on a CLR user-defined type column, the implementation of the
type must support binary ordering. For more information, see
CLR user-defined types.