SQL - Foreign Key


Advertisements

A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key.

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

The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Example

Consider the structure of the following two tables.

CUSTOMERS table

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

ORDERS table

CREATE TABLE ORDERS (
   ID          INT        NOT NULL,
   DATE        DATETIME, 
   CUSTOMER_ID INT references CUSTOMERS(ID),
   AMOUNT     double,
   PRIMARY KEY (ID)
);

If the ORDERS table has already been created and the foreign key has not yet been set, the use the syntax for specifying a foreign key by altering a table.

ALTER TABLE ORDERS 
   ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL syntax.

ALTER TABLE ORDERS
   DROP FOREIGN KEY;
sql-rdbms-concepts.htm

Useful Video Courses


Video

Oracle SQL Online Training

42 Lectures 5 hours

Anadi Sharma

Video

Oracle PL/SQL Online Training

14 Lectures 2 hours

Anadi Sharma

Video

T-SQL Online Training

44 Lectures 4.5 hours

Anadi Sharma

Video

SQL Masterclass: SQL for Data Analytics

Most Popular

94 Lectures 7 hours

Abhishek And Pukhraj

Video

Learn Database Design with MySQL

31 Lectures 6 hours

Eduonix Learning Solutions

Advertisements