Foreign key

Foreign key in SQL Server

A foreign key is a way to enforce referential integrity within your SQL Server database.

A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table

The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be created using either a CREATE TABLE statement or an ALTER TABLE statement.

The foreign key constraint prevents invalid data form being inserted into the foreign key column.

Example 1

CREATE TABLE products

( product_id INT constraint product_pk PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

CREATE TABLE inventory

(

  inventory_id INT constraint inventory_pk  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)

);

Example 2

CREATE TABLE products

(

product_name VARCHAR(50) NOT NULL,

  location VARCHAR(50) NOT NULL,

  category VARCHAR(25)

  CONSTRAINT products_pk PRIMARY KEY (product_name, location)

);

CREATE TABLE inventory

(

 inventory_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  location VARCHAR(50) NOT NULL,

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product FOREIGN KEY (product_name, location)

  REFERENCES products (product_name, location)

);

Foreign key - Using ALTER TABLE statement

Example 1

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id);

Example 2

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product

    FOREIGN KEY (product_name, location)

    REFERENCES products (product_name, location);

Foreign key with DELETE  Cascade

ON DELETE CASCADE

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

Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.

CREATE TABLE products

( product_id INT PRIMARY 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

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON DELETE CASCADE;

Foreign key with UPDATE Cascade

ON UPDATE

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

CREATE TABLE products

( product_id INT PRIMARY 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 update CASCADE

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON update CASCADE;

Foreign key with NO ACTION

NO ACTION

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

This is the default behaviour. No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.

CREATE TABLE products

( product_id INT PRIMARY 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 update no action

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON update no action;

SET NULL

It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is set to NULL when the parent data is deleted or updated.
Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.

CREATE TABLE products

( product_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

CREATE TABLE inventory

( inventory_id INT PRIMARY KEY,

  product_id INT,

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON DELETE SET NULL

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON DELETE SET NULL;

SET DEFAULT

It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is set to their default values when the parent data is deleted or updated.
Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.

CREATE TABLE products

( product_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

CREATE TABLE inventory

( inventory_id INT PRIMARY KEY,

  product_id  INT default(0),

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON delete set default

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON  delete set null

        Drop the foreign key

ALTER TABLE inventory

DROP CONSTRAINT fk_inv_product_id;