Definition:
A 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.You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a 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.
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.
GO
Ex: SalesPerson (table)
Although 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 Sales.SalesPerson table, and the salesperson's ID is used 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 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.
When a FOREIGN KEY constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure that all values, except NULL, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint. However, by specifying WITH NOCHECK, the Database Engine can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column.
The WITH NOCHECK option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.
However, you should be careful when you add a constraint without checking existing data because this bypasses the controls in the Database Engine that enforce the data integrity of the table.
ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT FK_NAME FOREIGN KEY(COL_NAME) REFERENCES PARENT_TABLE(PARENT_TABLE_COL)
You can disable existing FOREIGN KEY constraints forspecific operations, such as INSERT operations, UPDATE operations, and replication processing.
Any cascading actions defined on a related primary key will not be performed on rows that contain foreign keys that are disabled.
Disable existing constraint:
ENABLE FOREIGN KEY CONSTRAINTS:
CASCADING REFERENTIAL INTEGRITY:
By 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.
The DELETE statement conflicted with the REFERENCE constraint "fk_sales". The conflict occurred in database "Northwind", table "dbo.sales", column 'id'.
Need Further Study:
ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger.
For tables that have INSTEAD OF UPDATE triggers, the following cannot be specified: ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UDATE SET DEFAULT.
Need Further Study:
Cascading referential actions fire the AFTER UPDATE or AFTER DELETE triggers in the following manner:
All the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.
If there are any AFTER triggers defined on the affected tables, these triggers fire after all cascading actions are performed. These triggers fire in opposite order of the cascading action. If there are multiple triggers on a single table, they fire in random order, unless there is a dedicated first or last trigger for the table. This order is as specified by using sp_settriggerorder.
If multiple cascading chains originate from the table that was the direct target of an UPDATE or DELETE action, the order in which these chains fire their respective triggers is unspecified. However, one chain always fires all its triggers before another chain starts firing.
An AFTER trigger on the table that is the direct target of an UPDATE or DELETE action fires regardless of whether any rows are affected. There are no other tables affected by cascading in this case.
If any one of the previous triggers perform UPDATE or DELETE operations on other tables, these actions can start secondary cascading chains. These secondary chains are processed for each UPDATE or DELETE operation at a time after all triggers on all primary chains fire. This process may be 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 perform DELETE or UPDATE operations that start additional cascading chains and triggers.
If an error is generated inside any particular cascading referential action chain, an error is raised, no AFTER triggers are fired in that chain, and the DELETE or UPDATE operation that created the chain is rolled back.
A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. However, an AFTER trigger on a table targeted by a cascading action can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.
Individual DELETE or UPDATE statements can start a series of cascading referential actions. For example, a database contains three tables: TableA, TableB, and TableC. A foreign key in TableB is defined with ON DELETE CASCADE against the primary key in TableA. A foreign key in TableC is defined with ON DELETE CASCADE against the primary key in TableB. If a DELETE statement deletes rows in TableA, the operation also deletes any rows in TableB that have foreign keys matching the deleted primary keys in TableA, and then deletes any rows in TableC that have foreign keys that match the deleted primary keys in TableB.
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.
Creating an index on a foreign key is often useful for the following reasons:
SQL Server does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain (which reference other tables), or the number of FOREIGN KEY constraints owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints is limited by your hardware configuration and by the design of your 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. Consider the cost of enforcing FOREIGN KEY constraints when you design your database and applications.
SELF-REFERENCING TABLES:
A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table. These are called self-referencing tables. For example, consider an employee table that contains three columns: employee_number, employee_name, and manager_employee_number. Because the manager is also an employee, there is a foreign key relationship from the manager_employee_number column to the employee_number column.
To modify a FOREIGN KEY constraint, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition.
Querying the sys.foreign_keys catalog view returns the following values that indicate the cascading referential constraint specified for a foreign key.
The UPDATE_RULE and DELETE_RULE columns returned by sp_fkeys and sp_foreignkeys return 0 when CASCADE, SET NULL, or SET DEFAULT is specified; and return 1 when NO ACTION is specified or is the default.
When a foreign key is specified as the object of sp_help, the output result set contains the following columns.
A 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.You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a 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.
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.
Column Level
USE AdventureWorks2008
GO
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
);
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
);
GO
Table Level
CREATE TABLE ProductSales
(
SalesID INT,
ProductID INT,
SalesPerson VARCHAR(25)
CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),
CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
);
(
SalesID INT,
ProductID INT,
SalesPerson VARCHAR(25)
CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),
CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
);
GO
Alter Table Statement to create Foreign Key
ALTER TABLE ProductSales
ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
GO
Alter Table Statement to Drop Foreign Key
ALTER TABLE ProductSales
DROP CONSTRAINT fk_productSales_pid;
DROP CONSTRAINT fk_productSales_pid;
GO
Ex: SalesPerson (table)
SalesPersonid (Pk)
Sales orderheader(table)
SalesPersonid (Fk)
Ex:
GO
create table country(id int,name varchar(100))
create table sales(sales_id int,id int,amount money)
alter table country add constraint pk_country primary key(id)
alter table sales add constraint pk_sales primary key(sales_id)
alter table sales add constraint fk_sales foreign key(id) references country(id)
GO
Msg 8111, Level 16, State 1, Line 7
Cannot define PRIMARY KEY constraint on nullable column in table 'sales'.
Msg 1750, Level 16, State 0, Line 7
Could not create constraint. See previous errors.
In MS sql server By default column will accept NULL values.
GO
create table country(id int not null,name varchar(100))
create table sales(sales_id int not null,id int ,amount money)
alter table country add constraint pk_country primary key(id)
alter table sales add constraint pk_sales primary key(sales_id)
alter table sales add constraint fk_sales foreign key(id) references country(id)
GO
Although 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 Sales.SalesPerson table, and the salesperson's ID is used 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 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.
When a FOREIGN KEY constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure that all values, except NULL, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint. However, by specifying WITH NOCHECK, the Database Engine can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column.
The WITH NOCHECK option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.
However, you should be careful when you add a constraint without checking existing data because this bypasses the controls in the Database Engine that enforce the data integrity of the table.
ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT FK_NAME FOREIGN KEY(COL_NAME) REFERENCES PARENT_TABLE(PARENT_TABLE_COL)
You can disable existing FOREIGN KEY constraints forspecific operations, such as INSERT operations, UPDATE operations, and replication processing.
· INSERT and UPDATE statements
Disabling a FOREIGN KEY constraint enables data in the table to be modified without being validated by the constraints. Disable a FOREIGN KEY constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database.
· Replication processing
Disable a FOREIGN KEY constraint during replication if the constraint is specific to the source database. When a table is replicated, the table definition and data is copied from the source database to a destination database. If the FOREIGN KEY constraints are specific to the source database but are not disabled during replication, they may unnecessarily prevent new data from being entered in the destination database.
Any cascading actions defined on a related primary key will not be performed on rows that contain foreign keys that are disabled.
Disable existing constraint:
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
ENABLE FOREIGN KEY CONSTRAINTS:
ALTER TABLE table_name CHECK CONSTRAINT constraint_name
CASCADING REFERENTIAL INTEGRITY:
By 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.
· [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
· [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
NO ACTION
The Database Engine raises an error and the delete or update action on the row in the parent table is rolled back.
GO
create table country(id int not null,name varchar(100))
create table sales(sales_id int not null,id int ,amount money)
alter table country add constraint pk_country primary key(id)
alter table sales add constraint pk_sales primary key(sales_id)
alter table sales add constraint fk_sales foreign key(id) references country(id)
ON update NO ACTION ON delete no action
GO
insert into country values(1,'india
insert into country values(2,'Canada
insert into country values(3,'USA
insert into sales values(100,1,10000.789)
insert into sales values(101,1,12500)
insert into sales values(102,3,15500)
Delete from country where id=1
update country set id=4
where name='india
The UPDATE statement conflicted with the REFERENCE constraint "fk_sales". The conflict occurred in database "Northwind", table "dbo.sales", column 'id'.
CASCADE
Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table.
CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key.
ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger.
ON UPDATE CASCADE cannot be specified for tables that have INSTEAD OF UPDATE triggers.
ALTER TABLE SalesHistory
ADD CONSTRAINT fk_SalesHistoryProductID FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE ON UPDATE CASCASE
SET NULL
All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is updated or deleted. For this constraint to execute, the foreign key columns must be nullable. Cannot be specified for tables that have INSTEAD OF UPDATE triggers.
ALTER TABLE SalesHistory
ADD CONSTRAINT fk_SalesHistoryCustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE SET NULL ON UPDATE SET NULL
SET DEFAULT
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value.
All foreign key columns of the target table must have a default definition for this constraint to execute. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column.
Any nonnull values that are set because of ON DELETE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint.
Cannot be specified for tables that have INSTEAD OF UPDATE triggers.
Need Further Study:
ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger.
For tables that have INSTEAD OF UPDATE triggers, the following cannot be specified: ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UDATE SET DEFAULT.
Need Further Study:
All the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.
If there are any AFTER triggers defined on the affected tables, these triggers fire after all cascading actions are performed. These triggers fire in opposite order of the cascading action. If there are multiple triggers on a single table, they fire in random order, unless there is a dedicated first or last trigger for the table. This order is as specified by using sp_settriggerorder.
If multiple cascading chains originate from the table that was the direct target of an UPDATE or DELETE action, the order in which these chains fire their respective triggers is unspecified. However, one chain always fires all its triggers before another chain starts firing.
An AFTER trigger on the table that is the direct target of an UPDATE or DELETE action fires regardless of whether any rows are affected. There are no other tables affected by cascading in this case.
If any one of the previous triggers perform UPDATE or DELETE operations on other tables, these actions can start secondary cascading chains. These secondary chains are processed for each UPDATE or DELETE operation at a time after all triggers on all primary chains fire. This process may be 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 perform DELETE or UPDATE operations that start additional cascading chains and triggers.
If an error is generated inside any particular cascading referential action chain, an error is raised, no AFTER triggers are fired in that chain, and the DELETE or UPDATE operation that created the chain is rolled back.
A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. However, an AFTER trigger on a table targeted by a cascading action can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.
Individual DELETE or UPDATE statements can start a series of cascading referential actions. For example, a database contains three tables: TableA, TableB, and TableC. A foreign key in TableB is defined with ON DELETE CASCADE against the primary key in TableA. A foreign key in TableC is defined with ON DELETE CASCADE against the primary key in TableB. If a DELETE statement deletes rows in TableA, the operation also deletes any rows in TableB that have foreign keys matching the deleted primary keys in TableA, and then deletes any rows in TableC that have foreign keys that match the deleted primary keys in TableB.
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.
Creating an index on a foreign key is often useful for the following reasons:
· Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.
· Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the FOREIGN KEY constraint of one table with the primary or unique key column or columns in the other table. An index enables the Database Engine to quickly find related data in the foreign key table. However, creating this index is not required. Data from two related tables can be combined even if no PRIMARY KEY or FOREIGN KEY constraints are defined between the tables, but 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 its criteria.
SQL Server does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain (which reference other tables), or the number of FOREIGN KEY constraints owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints is limited by your hardware configuration and by the design of your 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. Consider the cost of enforcing FOREIGN KEY constraints when you design your database and applications.
SELF-REFERENCING TABLES:
A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table. These are called self-referencing tables. For example, consider an employee table that contains three columns: employee_number, employee_name, and manager_employee_number. Because the manager is also an employee, there is a foreign key relationship from the manager_employee_number column to the employee_number column.
To modify a FOREIGN KEY constraint, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition.
Value
|
Description
|
| 0 | NO ACTION |
| 1 | CASCADE |
| 2 | SET NULL |
| 3 | SET DEFAULT |
When a foreign key is specified as the object of sp_help, the output result set contains the following columns.
Column name
|
Data type
|
Description
|
| delete_action | nvarchar(9) | Indicates whether the delete action is CASCADE, SET NULL, SET DEFAULT, NO ACTION, or N/A (not applicable). |
| update_action | nvarchar(9) | Indicates whether the update action is CASCADE, SET NULL, SET DEFAULT, NO ACTION, or N/A (not applicable). |
No comments:
Post a Comment