Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Primary and Foreign Key Constraints
In this articleApplies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed InstancePrimary keys and foreign keys are two types of constraints that can be used to enforce data integrity in SQL Server tables. These are important database objects. Primary Key ConstraintsA table typically has a column or combination of columns that contain values 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. Because primary key constraints guarantee unique data, they are frequently defined on an identity column. When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique. As shown in the following illustration, the
Foreign Key ConstraintsA foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables 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 the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table. For example, the A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x) increases the limit for the number of other tables and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:
Indexes on Foreign Key ConstraintsUnlike primary key constraints, creating a foreign key constraint does not automatically create a corresponding index. However, manually creating an index on a foreign key is often useful for the following reasons:
Referential IntegrityAlthough the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. For example, if the row for a salesperson is deleted from the A foreign key constraint prevents this situation. The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the foreign key constraint of another table. To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data. Cascading Referential IntegrityBy using cascading referential integrity constraints, you can define the actions that the Database Engine takes when a user tries to delete or update a key to which existing foreign keys point. The following cascading actions can be defined. NO ACTION CASCADE SET NULL SET DEFAULT 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. Triggers and Cascading Referential ActionsCascading referential actions fire the AFTER UPDATE or AFTER DELETE triggers in the following manner:
Next stepsThe following table lists the common tasks associated with primary key and foreign key constraints.
FeedbackSubmit and view feedback for Additional resources
Additional resourcesIn this articleIn which normal form does an attribute that is not fully dependent on the primary key is split into a separate table?Third Normal Form
A more formal definition is: A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key.
What does it mean for a column to be functionally dependent on another column quizlet?A column is functionally dependent on another column if for each value of the first column, there is only one value for the second column. T. The third normal form creates columns that are independent of the primary key.
What is a non key column quizlet?A column is a nonkey attribute when it is not part of the primary key. True. The primary key in a table is a determinant but candidate keys are not determinants.
What is augmented functional dependency?augmented functional dependency. functional dependency that contains an existing functional dependency. Ex. If a functional dependency A --> B exists in a relation then: A, C ---> B is an augmented functional dependency.
|