SQL FOREIGN KEY Constraint

A FOREIGN KEY is used to link two table together and it is also known as the name of referencing key.

Actually, A Foreign Key is a combination of columns whose values match a Primary Key in a different table.

EXAMPLE: FOREIGN KEY

"CUSTOMERS" Table

The "CUSTOMER_ID" column is the PRIMARY KEY column in the "CUSTOMERS" table.

CREATE TABLE CUSTOMERS (
  CUSTOMER_ID INT   NOT NULL PRIMARY KEY,
  NAME VARCHAR (100)   NOT NULL,
  EMAIL VARCHAR (100),
  ADDRESS CHAR (200),
  SALARY DECIMAL (100, 3)
);

"ORDERS" Table

The "CUSTOMER_ID" column in the "ORDERS" table is the FOREIGN KEY column.

CREATE TABLE ORDERS (
  OID INT   NOT NULL,
  DATE DATETIME,
  FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID),
  AMOUNT DOUBLE,
  PRIMARY KEY (CUSTOMER_ID)
);

FOREIGN KEY Description

  • The "CUSTOMER_ID" column in the "ORDERS" table points to the "CUSTOMER_ID" column in the "CUSTOMERS" table.

ALTER TABLE - FOREIGN KEY

To CREATE a FOREIGN KEY constraint on the "CUSTOMER_ID" column when the "ORDERS" table is already created, use the following SQL Syntax:

ALTER TABLE ORDERS
ADD FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID);

DROP TABLE - FOREIGN KEY

To DROP a FOREIGN KEY constraint on the "ORDERS" table, use the following SQL Syntax:

ALTER TABLE ORDERS
DROP FOREIGN KEY;

Policy

GDATAMART.COM is optimized for learning & job information; The users try to improve your knowledge and practical experience through this site but we cannot warrant full correctness of all content... Learn More → | Sitemap →

© 2020 GDATAMART | All Rights Reserved