What are the main restrictions in DBMS

Primary key and foreign key restrictions

Scope of application:SQL Server 2016 (13.x) and higher Azure SQL database Azure SQL managed instance

Primary keys and foreign keys are two types of constraints that can be used to enforce data integrity on SQL Server tables. These are important database objects.

This topic contains the following sections:

PRIMARY KEY constraints

Foreign key constraints

Related tasks

PRIMARY KEY constraints

A table usually has a column or combination of columns that contain values ​​that uniquely identify each row in the table. This column or combination of columns is known as the table's primary key (PK) and enforces the table's entity integrity. Because PRIMARY KEY constraints guarantee unique data, they are often defined on an identity column.

When you specify a PRIMARY KEY constraint on a table, the Database Engine enforces the uniqueness of the data by automatically creating a unique index on the primary key columns. The index also provides quick access to data when the primary key is used in queries. When a PRIMARY KEY constraint is defined on multiple columns, values ​​within a column can be duplicated; however, any combination of the values ​​of all columns contained in the definition of the PRIMARY KEY constraint must be unique.

As shown in the figure below, the columns ProductID and VendorID in the Purchasing.ProductVendor -Table form a compound PRIMARY KEY constraint on that table. This ensures that every line in the ProductVendor-Table a unique combination of ProductID and VendorID having. This prevents the insertion of duplicate lines.

  • A table can have only one PRIMARY KEY constraint.

  • A primary key must not exceed 16 columns and a total key length of 900 bytes.

  • The index generated by a PRIMARY KEY constraint must not cause the number of indexes on table 999 to exceed ungrouped indexes and any clustered index.

  • If CLUSTERED or NONCLUSTERED is not specified for a PRIMARY KEY constraint, CLUSTERED is used unless there are clustered indexes on the table.

  • All columns for which a PRIMARY KEY restriction has been defined must be defined as not equal to NULL. If NULL is not specified, all columns that have a PRIMARY KEY constraint applied are NULL set to NOT NULL.

  • When defining a primary key on a column of a CLR custom type, the implementation of the type must support binary sort order.

Foreign key constraints

A foreign key (FS) is a column or combination of columns that is used to establish and enforce a link between the data in two tables in order to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when a column or columns in one table reference the column or columns with the primary key value in another table. This column becomes a foreign key in the second table.

For example, suppose the Sales.SalesOrderHeader -Table assigns a foreign key link Sales.SalesPerson -Table because there is a logical relationship between “Sales Orders” and “Sales People”. The SalesPersonID Column of the SalesOrderHeader -Table matches the primary key column of the SalesPerson Table match. The SalesPersonID Column of the SalesOrderHeader -Table is the foreign key to the SalesPerson -Table. When you create this foreign key relationship, a value for SalesPersonID not in the SalesOrderHeader Table, if it is not already in the SalesPerson -Table exists.

A table can refer to a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x) increases the limit on the number of other tables and columns that can reference columns in a single table (incoming references) from 253 to 10,000. (Compatibility level 130 or higher required.) The following restrictions apply to the increase:

  • More than 253 foreign key references are only supported for DELETE DML operations. UPDATE and MERGE operations are not supported.

  • A table with a foreign key reference to itself is also limited to 253 foreign key references.

  • No more than 253 foreign key references are currently possible for columnstore indexes, memory-optimized tables, stretch-enabled databases, or partitioned foreign key tables.

Foreign key constraint indexes

In contrast to PRIMARY KEY restrictions, a corresponding index is not automatically generated when a FOREIGN KEY restriction is created. However, creating an index on a foreign key manually is recommended for the following reasons:

  • Foreign key columns are often used in join criteria when combining data from related tables in queries by finding the match between the column or columns in the FOREIGN KEY constraint on one table and the column or columns in a primary or unique key in the other table . An index enables the database engine to quickly find the related data in the foreign key table. However, it is not mandatory to create an index. Data from two related tables can be combined even if no PRIMARY KEY constraint or FOREIGN KEY constraint is defined between these tables. However, a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as a criterion.

  • Changes to PRIMARY KEY constraints are checked using FOREIGN KEY constraints in related tables.

Referential Integrity

A FOREIGN KEY restriction is primarily used to control the data that can be stored in the foreign key table; however, it also controls the changes made to data in the primary key table. For example, if the line for a sales representative is from the Sales.SalesPerson Table deleted, but the sales representative's ID is still in the Sales.SalesOrderHeader Table is used, the relational integrity between the two tables is destroyed. The deleted seller's purchase orders are in the SalesOrderHeader -Table orphaned because there is no longer a link to data in SalesPerson -Table exists.

A FOREIGN KEY constraint prevents such a situation. The constraint enforces referential integrity by ensuring that changes to data in the primary key table cannot be made if those changes cause the link to data in the foreign key table to become invalid. If an attempt is made to delete a row in a primary key table or to change a primary key value, this action generates an error if the deleted or changed primary key value matches a value in the FOREIGN KEY constraint of another table. To successfully modify or delete a row in a FOREIGN KEY constraint, you must first either delete the foreign key information in the foreign key table or modify it so that the foreign key is linked to other primary key information.

Cascading referential integrity

By using referential integrity constraints, you can define the actions the Database Engine takes when a user tries to delete or update a key by pointing to the foreign key. The following cascading actions can be defined.

NO ACTION
The database engine fails and the action to delete or update the row in the parent table is rolled back.

CASCADE
If this row is updated or deleted in the parent table, the corresponding rows in the referring table are updated or deleted. CASCADE cannot be specified if a timestamp Column is part of a foreign key or the referenced key. ON DELETE CASCADE cannot be specified on a table that has an INSTEAD OF trigger. ON UPDATE CASCADE cannot be specified on tables that have INSTEAD OF UPDATE triggers.

SET NULL
All values ​​that make up the foreign key are set to NULL when the corresponding row in the parent table is updated or deleted. The foreign key columns must allow NULL values ​​to apply this restriction. This option cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET DEFAULT
All of the values ​​that make up the foreign key are set to their default values ​​when the corresponding row in the parent table is updated or deleted. All foreign key columns must have default definitions for this restriction to take effect. If a column is null and no explicit default is set, NULL is used as the implicit default for the column. This option cannot be specified for tables that have INSTEAD OF UPDATE triggers.

CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined for tables that have referential relationships with one another. If the database engine detects the NO ACTION setting, processing stops and associated CASCADE, SET NULL, and SET DEFAULT actions are rolled back. If a DELETE statement results in a combination of CASCADE, SET NULL, SET DEFAULT and NO ACTION actions, all CASCADE, SET NULL and SET DEFAULT actions are applied before the database engine after the possible specification of NO ACTION is looking for.

Triggers and cascading referential actions

Cascading referential actions fire AFTER UPDATE or AFTER DELETE triggers in the following ways:

  • Any cascading referential actions triggered directly by the original DELETE or UPDATE statement are executed first.

  • If AFTER triggers have been defined for the tables concerned, these triggers are triggered after all cascading actions have been carried out. These triggers are fired in reverse order to the cascading action. If there are multiple triggers for a single table, they will fire in random order unless there is a dedicated first or last trigger for the table. This order is specified using sp_settriggerorder.

  • If multiple cascading chains originate in the table that was the direct target of the UPDATE or DELETE action, the order in which these chains fire their respective triggers is not specified. However, one chain always triggers all of its triggers before another chain starts triggering.

  • An AFTER trigger for the table that is the direct target of an UPDATE or DELETE action is triggered regardless of whether any rows are affected or not. In this case, no other tables are affected by the cascading.

  • If any of the previous triggers perform UPDATE or DELETE operations on other tables, those actions can start secondary cascading chains. These secondary chains are processed for each UPDATE or DELETE operation after all triggers for all primary chains have fired. This process can be repeated recursively for all subsequent UPDATE or DELETE operations.

  • Executing CREATE, ALTER, DELETE or other operations in the Data Definition Language (DDL) can trigger DDL triggers. This can eventually lead to the execution of DELETE or UPDATE operations, which start additional cascading chains and triggers.

  • If an error occurs within a specific cascading referential action chain, an error is triggered, no AFTER triggers are triggered in this chain, and the DELETE or UPDATE operation used to create the chain is rolled back.

  • A table with an INSTEAD OF trigger cannot also have a REFERENCES clause indicating a cascading action. However, an AFTER trigger in a table that is the target of a cascading action can execute an INSERT, UPDATE, or DELETE statement in another table or view that fires an INSTEAD OF trigger defined for that object.

Related tasks

The following table lists the common tasks associated with PRIMARY KEY and FOREIGN KEY restrictions.

tasktheme
Describes how to create a primary key.Creation of primary keys
Describes how to delete a primary key.Deletion of primary keys
Describes how to change a primary key.Changing primary keys
Describes how to create foreign key relationships.Creating Foreign Key Relationships
Describes how to change foreign key relationships.Changing foreign key relationships
Describes how to delete foreign key relationships.Deletion of primary key-foreign key relationships
Describes how to display foreign key properties.View foreign key properties
Describes how to disable FOREIGN KEY restrictions on replication.Disable foreign key restrictions for replication
Describes how to disable FOREIGN KEY constraints during an INSERT or an UPDATE statement.Disable foreign key restrictions with INSERT and UPDATE statements