In this article
Primary keys and foreign tricks are two varieties of constraints that have the right to be used to force data truth in SQL Server tables. This are crucial database objects.
You are watching: A(n) ____ is a field or set of fields used to uniquely identify each row.
This topic contains the adhering to sections.
Primary vital Constraints
Foreign an essential Constraints
Primary crucial Constraints
A table generally has a obelisk or mix of columns the contain worths that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Since primary crucial constraints guarantee distinctive data, they room frequently defined on an identification column.
When friend specify a primary key constraint because that a table, the Database Engine enforces data uniqueness by immediately creating a distinct index because that the primary vital columns. This index additionally permits fast access to data once the primary vital is supplied in queries. If a primary key constraint is characterized on much more than one column, values may be duplicated within one column, however each mix of worths from all the columns in the primary an essential constraint definition must it is in unique.
As displayed in the complying with illustration, the ProductID and also VendorID columns in the Purchasing.ProductVendor table kind a composite primary crucial constraint because that this table. This provides sure the every row in the ProductVendor table has actually a unique mix of ProductID and also VendorID. This prevents the insertion the duplicate rows.
A table have the right to contain just one primary vital constraint.
A primary an essential cannot exceed 16 columns and also a total crucial length the 900 bytes.
The index produced by a primary crucial constraint cannot cause the variety of indexes top top the table come 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 index on the table.
All columns identified within a primary key constraint should be characterized as no null. If nullability is no specified, every columns participating in a primary key constraint have actually their nullability collection to not null.
If a primary crucial is identified on a CLR user-defined type column, the implementation of the kind must support binary ordering.
Foreign crucial Constraints
A foreign an essential (FK) is a obelisk or combination of columns that is used to establish and also enforce a link between the data in 2 tables to control the data that can be save in the foreign key table. In a foreign crucial reference, a connect is created between two tables once the column or columns that host the primary key value because that one table are referenced by the tower or columns in an additional table. This tower becomes a foreign an essential in the 2nd 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 in between sales orders and also salespeople. The SalesPersonID pillar in the SalesOrderHeader table matches the primary crucial column of the SalesPerson table. The SalesPersonID shaft in the SalesOrderHeader table is the foreign key to the SalesPerson table. By developing this foreign an essential relationship, a value for SalesPersonID cannot be placed into the SalesOrderHeader table if it does not currently exist in the SalesPerson table.
A table have the right to reference a best of 253 various other tables and also columns as international keys (outgoing references). SQL Server 2016 (13.x) rises the limit for the number of other table and also columns that have the right to reference columns in a single table (incoming references), indigenous 253 to 10,000. (Requires at the very least 130 compatibility level.) The increase has the adhering to restrictions:
Greater than 253 foreign key references are only supported because that DELETE DML operations. UPDATE and MERGE operations space not supported.
A table through a foreign vital reference to chin is still minimal to 253 foreign key references.
Greater than 253 foreign vital references room not currently available for columnstore indexes, memory-optimized tables, stretch Database, or partitioned foreign crucial tables.
Indexes top top Foreign vital Constraints
Unlike primary crucial constraints, producing a foreign key constraint does not automatically create a corresponding index. However, manually creating an table of contents on a foreign an essential is often helpful for the complying with reasons:
Foreign key columns are frequently used in sign up with criteria once the data from associated tables is linked in queries by corresponding the shaft or columns in the foreign crucial constraint that one table with the primary or unique vital column or columns in the various other table. One index allows the Database Engine to quickly uncover related data in the foreign crucial table. However, creating this table of contents is not required. Data indigenous two connected tables deserve to be combined even if no primary vital or foreign vital constraints space defined in between the tables, but a foreign vital relationship between two tables shows that the two tables have been optimized to be an unified in a query that provides the keys as that is criteria.
Changes to primary key constraints are checked with foreign an essential constraints in related tables.
Although the main purpose that a foreign crucial constraint is to control the data that deserve to be stored in the foreign an essential table, it additionally controls transforms to data in the primary key table. Because that example, if the row for a salesperson is turned off from the Sales.SalesPerson table, and also the salesperson"s id is offered for sales orders in the Sales.SalesOrderHeader table, the relational integrity in between the two tables is broken; the turned off salesperson"s sales orders room orphaned in the SalesOrderHeader table there is no a attach to the data in the SalesPerson table.
A foreign vital constraint prevents this situation. The constraint enforces referential verity by guaranteeing that changes cannot be made to data in the primary vital table if those transforms invalidate the connect to data in the foreign key table. If an effort is made to delete the row in a primary key table or to readjust a primary vital value, the activity will fail when the deleted or adjusted primary vital value corresponds to a worth in the foreign key constraint of another table. To successfully adjust or delete a heat in a foreign crucial constraint, friend must an initial either delete the foreign an essential data in the foreign an essential table or readjust the foreign an essential data in the foreign an essential table, which links the foreign vital to various primary an essential data.Cascading Referential Integrity
By using cascading referential truth constraints, girlfriend can define the actions that the Database Engine takes when a user tries come delete or update a crucial to which existing foreign keys point. The following cascading actions can be defined.
NO ACTIONThe Database Engine raises one error and also the delete or update action on the heat in the parental table is rolled back.
CASCADECorresponding rows are updated or deleted in the referencing table as soon as that heat is update or deleted in the parent table. CASCADE can not be stated if a timestamp tower is component of one of two people the foreign key or the referenced key. Top top DELETE CASCADE cannot be mentioned for a table that has an rather OF DELETE trigger. ON update CASCADE can not be stated for tables that have INSTEAD OF upgrade triggers.
SET NULLAll the worths that make up the foreign an essential are collection to NULL as soon as the matching row in the parental table is updated or deleted. Because that this constraint to execute, the foreign vital columns need to be nullable. Can not be stated for tables that have actually INSTEAD OF upgrade triggers.
SET DEFAULTAll the worths that consist of the foreign vital are set to their default worths if the matching row in the parental table is to update or deleted. Because that this constraint come execute, all foreign vital columns must have actually default definitions. If a shaft is nullable, and also 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 upgrade triggers.
CASCADE, collection NULL, collection DEFAULT and NO action can be merged on tables that have actually referential relationships through each other. If the Database Engine to meet NO ACTION, it stops and rolls ago related CASCADE, set NULL and collection DEFAULT actions. As soon as a DELETE statement causes a combination of CASCADE, set NULL, collection DEFAULT and NO action actions, every the CASCADE, set NULL and collection DEFAULT plot are applied before the Database Engine checks for any kind of NO ACTION.
Triggers and Cascading Referential Actions
Cascading referential actions fire the AFTER update or after DELETE root cause in the following manner:
All the cascading referential actions directly caused through the original DELETE or UPDATE are performed first.
If many cascading chain originate from the table the was the straight target of an upgrade or DELETE action, the stimulate in which this chains fire their respective triggers is unspecified. However, one chain constantly fires every its triggers before an additional chain start firing.
An AFTER cause on the table the is the direct target the an upgrade or DELETE activity fires regardless of whether any rows space affected. There space no various other tables influenced by cascading in this case.
If any kind of one that the previous triggers perform UPDATE or DELETE operations on other tables, these actions can start secondary cascading chains. These an additional chains are processed because that each upgrade or DELETE procedure at a time after all triggers on all major 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 update operations that start added cascading chains and triggers.
If one error is produced inside any specific cascading referential activity chain, an error is raised, no after triggers space fired in that chain, and also the DELETE or UPDATE operation that produced the chain is rolling back.
A table that has actually an rather OF create cannot also have a referrals clause that mentions a cascading action. However, one AFTER trigger on a table targeted by a cascading activity can execute one INSERT, UPDATE, or DELETE statement on an additional table or check out that fires an instead OF trigger defined on that object.
The adhering to table perform the typical tasks connected with primary crucial and foreign crucial constraints.
|Describes how to produce a primary key.||Create main Keys|
|Describes how to delete a major key.||Delete primary Keys|
|Describes just how to change a main key.||Modify major Keys|
|Describes exactly how to develop foreign crucial relationships||Create Foreign an essential Relationships|
|Describes exactly how to change foreign an essential relationships.||Modify Foreign crucial Relationships|
|Describes just how to delete foreign key relationships.||Delete Foreign crucial Relationships|
|Describes just how to see foreign an essential properties.||View Foreign an essential Properties|
|Describes exactly how to disable foreign key constraints for replication.|
See more: M3/S To Kg/S Ec - Gasoline (Kg/Sec) = 0
|Disable Foreign key Constraints for Replication|
|Describes how to disable foreign crucial constraints throughout an INSERT or update statement.||Disable Foreign an essential Constraints through INSERT and UPDATE Statements|