In this article
Primary keys and also foreign tricks are two types of constraints that can be supplied to force data integrity in SQL Server tables. These are vital database objects.
You are watching: Constraints are always enforced at the ____ level.
This topic consists of the complying with sections.
Primary key Constraints
Foreign crucial Constraints
Primary key Constraints
A table frequently has a obelisk or mix of columns that contain worths that uniquely identify each row in the table. This column, or columns, is dubbed the primary an essential (PK) the the table and also enforces the reality integrity that the table. Due to the fact that primary an essential constraints guarantee distinct data, they space frequently defined on an identity column.
When you specify a primary crucial constraint because that a table, the Database Engine enforces data uniqueness by immediately creating a unique index because that the primary vital columns. This index also permits fast accessibility to data as soon as the primary crucial is provided in queries. If a primary vital constraint is characterized on much more than one column, values might be replicated within one column, but each combination of worths from all the columns in the primary key constraint an interpretation must be unique.
As shown in the adhering to illustration, the ProductID and also VendorID columns in the Purchasing.ProductVendor table kind a composite primary vital constraint for this table. This makes sure that every row in the ProductVendor table has a unique mix of ProductID and VendorID. This prevents the insertion the duplicate rows.
A table can contain only one primary key constraint.
A primary an essential cannot exceed 16 columns and a total key length the 900 bytes.
The index produced by a primary an essential constraint cannot reason the number of indexes on the table to exceed 999 nonclustered indexes and also 1 clustered index.
If clustered or nonclustered is not specified for a primary crucial constraint, clustered is supplied if there no clustered table of contents on the table.
All columns identified within a primary an essential constraint have to be identified as no null. If nullability is not specified, every columns participating in a primary crucial constraint have their nullability set to not null.
If a primary crucial is characterized on a CLR user-defined type column, the implementation the the type must assistance binary ordering.
Foreign vital Constraints
A foreign vital (FK) is a pillar or mix of columns that is supplied to establish and enforce a link in between the data in 2 tables to regulate the data that have the right to be save in the foreign vital table. In a foreign vital reference, a connect is created between two tables when the column or columns that organize the primary vital value because that one table room referenced through the column or columns in an additional table. This column becomes a foreign crucial in the second table.
For example, the Sales.SalesOrderHeader table has actually a foreign an essential link to the Sales.SalesPerson table due to the fact that there is a logical relationship between sales orders and also salespeople. The SalesPersonID tower in the SalesOrderHeader table matches the primary vital column the the SalesPerson table. The SalesPersonID column in the SalesOrderHeader table is the foreign key to the SalesPerson table. By developing this foreign vital relationship, a worth for SalesPersonID cannot be inserted into the SalesOrderHeader table if that does not currently exist in the SalesPerson table.
A table have the right to reference a maximum of 253 other tables and also columns as international keys (outgoing references). SQL Server 2016 (13.x) boosts the border for the variety of other table and columns that have the right to reference columns in a solitary table (incoming references), from 253 come 10,000. (Requires at the very least 130 compatibility level.) The increase has the complying with restrictions:
Greater 보다 253 foreign vital references are just supported for DELETE DML operations. UPDATE and MERGE operations room not supported.
A table through a foreign crucial reference to chin is still restricted to 253 foreign key references.
Greater 보다 253 foreign an essential references space not currently accessible for columnstore indexes, memory-optimized tables, large Database, or partitioned foreign key tables.
Indexes top top Foreign vital Constraints
Unlike primary key constraints, developing a foreign key constraint walk not automatically create a corresponding index. However, manually developing an index on a foreign crucial is often beneficial for the adhering to reasons:
Foreign crucial columns are frequently used in sign up with criteria when the data from related tables is merged in queries by matching the shaft or columns in the foreign vital constraint that one table through the major or unique key column or columns in the various other table. An index enables the Database Engine to quickly uncover related data in the foreign key table. However, developing this index is no required. Data indigenous two related tables deserve to be linked even if no primary an essential or foreign key constraints space defined between the tables, yet a foreign an essential relationship in between two tables suggests that the two tables have been optimized come be an unified in a ask that supplies the keys as its criteria.
Changes come primary vital constraints space checked v foreign an essential constraints in connected tables.
Although the key purpose of a foreign key constraint is to control the data that deserve to be stored in the foreign key table, it likewise controls alters to data in the primary key table. For example, if the row for a salesperson is deleted from the Sales.SalesPerson table, and the salesperson"s id is provided for sales orders in the Sales.SalesOrderHeader table, the relational integrity between the two tables is broken; the deleted salesperson"s sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table.
A foreign vital constraint prevents this situation. The constraint enforces referential integrity by guaranteeing that transforms cannot be made to data in the primary crucial table if those alters invalidate the link to data in the foreign crucial table. If an effort is made come delete the heat in a primary vital table or to change a primary an essential value, the activity will fail as soon as the deleted or readjusted primary key value corresponds to a worth in the foreign an essential constraint of an additional table. Come successfully change or delete a row in a foreign crucial constraint, friend must first either delete the foreign an essential data in the foreign vital table or readjust the foreign key data in the foreign crucial table, which links the foreign an essential to different primary an essential data.Cascading Referential Integrity
By making use of cascading referential verity constraints, friend can specify the actions the the Database Engine takes when a user tries come delete or upgrade a crucial to i m sorry existing foreign keys point. The adhering to cascading actions can be defined.
NO ACTIONThe Database Engine raises one error and the delete or update action on the row in the parent table is rolled back.
CASCADECorresponding rows room updated or turned off in the express table once that row is update or deleted in the parental table. CASCADE can not be specified if a timestamp obelisk is part of either the foreign vital or the referenced key. ~ above DELETE CASCADE cannot be specified for a table that has an instead OF DELETE trigger. ON upgrade CASCADE can not be mentioned for tables that have actually INSTEAD OF upgrade triggers.
SET NULLAll the values that make up the foreign vital are set to NULL when the equivalent row in the parental table is to update or deleted. For this constraint come execute, the foreign an essential columns should be nullable. Cannot be specified for tables that have INSTEAD OF update triggers.
SET DEFAULTAll the values that consist of the foreign key are collection to their default values if the equivalent row in the parent table is update or deleted. Because that this constraint come execute, all foreign vital columns must have default definitions. If a obelisk is nullable, and there is no explicit default worth set, NULL becomes the implicitly default worth of the column. Can not be stated for tables that have INSTEAD OF update triggers.
CASCADE, collection NULL, collection DEFAULT and NO activity can be linked on tables that have referential relationships v each other. If the Database Engine encounters NO ACTION, it stops and rolls ago related CASCADE, collection NULL and collection DEFAULT actions. When a DELETE statement causes a mix of CASCADE, set NULL, collection DEFAULT and NO action actions, every the CASCADE, collection NULL and collection DEFAULT plot are applied before the Database Engine checks for any type of NO ACTION.
Triggers and Cascading Referential Actions
Cascading referential action fire the AFTER update or after ~ DELETE cause in the adhering to manner:
All the cascading referential actions directly caused by the initial DELETE or UPDATE are performed first.
If lot of cascading chains originate indigenous the table that was the straight target that an update or DELETE action, the order in which these chains fire their corresponding triggers is unspecified. However, one chain always fires all its root cause before an additional chain beginning firing.
An AFTER cause on the table that is the straight target of an update or DELETE activity fires regardless of whether any type of rows space affected. There are no various other tables affected by cascading in this case.
If any one that the vault triggers do UPDATE or DELETE work on various other tables, this actions can start an additional cascading chains. These second chains space processed for each upgrade or DELETE procedure at a time after every triggers ~ above all main chains fire. This process may it is in recursively repeated for subsequent UPDATE or DELETE operations.
Performing CREATE, ALTER, DELETE, or other data definition language (DDL) operations inside the triggers may cause DDL triggers to fire. This may subsequently carry out DELETE or upgrade operations that start added cascading chains and triggers.
If an error is generated inside any details cascading referential action chain, one error is raised, no ~ triggers space fired in that chain, and also the DELETE or UPDATE procedure that developed the chain is rolling back.
A table that has an instead OF create cannot also have a recommendations clause that specifies a cascading action. However, an AFTER create on a table target by a cascading activity can execute an INSERT, UPDATE, or DELETE statement on another table or see that fires an instead OF create defined on that object.
The following table list the common tasks connected with primary vital and foreign an essential constraints.
|Describes just how to develop a primary key.||Create primary Keys|
|Describes just how to delete a primary key.||Delete major Keys|
|Describes just how to modify a primary key.||Modify main Keys|
|Describes just how to develop foreign an essential relationships||Create Foreign vital Relationships|
|Describes just how to change foreign an essential relationships.||Modify Foreign key Relationships|
|Describes exactly how to delete foreign vital relationships.||Delete Foreign crucial Relationships|
|Describes just how to check out foreign an essential properties.||View Foreign vital Properties|
|Describes how to disable foreign vital constraints because that replication.|
See more: # Rita Masterson New Jersey Karen Harasses Ruben Martinez, Rita Masterson (@Ritapita0707)
|Disable Foreign vital Constraints for Replication|
|Describes exactly how to disable foreign an essential constraints during an INSERT or upgrade statement.||Disable Foreign key Constraints v INSERT and also UPDATE Statements|