what happens to foreign key in database if primary key values are deleted

---

SQL Server: Foreign Keys with cascade delete

This SQL Server tutorial explains how to use Foreign Keys with cascade delete in SQL Server with syntax and examples.

What is a strange key with Cascade DELETE in SQL Server?

A foreign central with pour delete means that if a tape in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is chosen a pour delete in SQL Server.

A foreign key with cascade delete tin can be created using either a CREATE TABLE statement or an Change Tabular array statement.

Create Foreign key with pour delete - Using CREATE Tabular array statement

Syntax

The syntax for creating a foreign key with pour delete using a CREATE TABLE statement in SQL Server (Transact-SQL) is:

CREATE Tabular array child_table (   column1 datatype [ NULL | Not Zippo ],   column2 datatype [ Nothing | Non Goose egg ],   ...    CONSTRAINT fk_name     FOREIGN KEY (child_col1, child_col2, ... child_col_n)     REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)     ON DELETE Cascade     [ ON UPDATE { NO Activeness | Pour | Prepare Null | Set DEFAULT } ]  );

child_table

The name of the child table that you wish to create.

column1, column2

The columns that you wish to create in the table. Each column must have a datatype. The cavalcade should either exist divers every bit NULL or Non Nada and if this value is left blank, the database assumes Nothing as the default.

fk_name

The name of the foreign key constraint that you wish to create.

child_col1, child_col2, ... child_col_n

The columns in child_table that will reference a primary central in the parent_table.

parent_table

The name of the parent table whose main primal volition be used in the child_table.

parent_col1, parent_col2, ... parent_col3

The columns that make up the master cardinal in the parent_table. The foreign key will enforce a link between this information and the child_col1, child_col2, ... child_col_n columns in the child_table.

ON DELETE Pour

It specifies that the child data is deleted when the parent data is deleted.

ON UPDATE

Optional. It specifies what to do with the child data when the parent data is updated. You take the options of NO ACTION, CASCADE, Gear up NULL, or SET DEFAULT.

NO ACTION

It is used in conjunction with ON DELETE or ON UPDATE. Information technology means that no action is performed with the kid information when the parent data is deleted or updated.

Cascade

It is used in conjunction with ON DELETE or ON UPDATE. It ways that the child information is either deleted or updated when the parent data is deleted or updated.

SET Zippo

Information technology is used in conjunction with ON DELETE or ON UPDATE. Information technology ways that the child data is gear up to NULL when the parent data is deleted or updated.

Prepare DEFAULT

It is used in conjunction with ON DELETE or ON UPDATE. It ways that the child data is set to their default values when the parent data is deleted or updated.

Instance

Let'southward wait at an example of how to create a strange cardinal with cascade delete in SQL Server (Transact-SQL) using the CREATE Table statement.

For example:

CREATE Tabular array products ( product_id INT Principal KEY,   product_name VARCHAR(50) NOT NULL,   category VARCHAR(25) );  CREATE Table inventory ( inventory_id INT PRIMARY KEY,   product_id INT Not NULL,   quantity INT,   min_level INT,   max_level INT,   CONSTRAINT fk_inv_product_id     Foreign Key (product_id)     REFERENCES products (product_id)     ON DELETE Cascade );

In this foreign fundamental example, we've created our parent table as the products tabular array. The products table has a primary central that consists of the product_id field.

Next, we've created a second table called inventory that will be the kid tabular array in this foreign key with pour delete example. We have used the CREATE TABLE argument to create a strange key on the inventory table called fk_inv_product_id. The foreign fundamental establishes a human relationship between the product_id column in the inventory table and the product_id column in the products table.

For this foreign primal, we take specified the ON DELETE Pour clause which tells SQL Server to delete the corresponding records in the child tabular array when the data in the parent table is deleted. So in this instance, if a product_id value is deleted from the products tabular array, the respective records in the inventory table that utilize this product_id will too be deleted.

Create a foreign key with pour delete - Using ALTER TABLE statement

Syntax

The syntax for creating a foreign key with cascade delete using an Alter TABLE statement in SQL Server (Transact-SQL) is:

Alter Tabular array child_table ADD CONSTRAINT fk_name     FOREIGN Central (child_col1, child_col2, ... child_col_n)     REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)     ON DELETE CASCADE;

child_table

The name of the child table that you wish to modify.

fk_name

The proper noun of the foreign key constraint that yous wish to create.

child_col1, child_col2, ... child_col_n

The columns in child_table that volition reference a primary cardinal in the parent_table.

parent_table

The name of the parent table whose principal key volition be used in the child_table.

parent_col1, parent_col2, ... parent_col3

The columns that brand upward the master key in the parent_table. The strange key volition enforce a link betwixt this data and the child_col1, child_col2, ... child_col_n columns in the child_table.

ON DELETE CASCADE

It specifies that the kid data is deleted when the parent data is deleted.

Example

Let's look at an case of how to create a foreign primal with cascade delete in SQL Server (Transact-SQL) using the Change TABLE argument.

For example:

ALTER Table inventory ADD CONSTRAINT fk_inv_product_id     FOREIGN Primal (product_id)     REFERENCES products (product_id)     ON DELETE Pour;

In this foreign central instance, we've created a foreign key on the inventory table called fk_inv_product_id that references the products tabular array based on the product_id field.

For this foreign key, nosotros take specified the ON DELETE CASCADE clause which tells SQL Server to delete the corresponding records in the child table when the data in the parent table is deleted. Then in this example, if a product_id value is deleted from the products tabular array, the corresponding records in the inventory tabular array that utilise this product_id will also be deleted.

robisonbeftedind.blogspot.com

Source: https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete.php

Share this post