×

Courses

Chapters

>
<

DBMS

DBMS Key Foreign | CrackEase

Foreign Key in DBMS

Foreign Key

A foreign key enforces a relationship between two tables by restricting values in a child table column to those that exist in a parent table's primary key column.

In other words, a foreign key is a column (or set of columns) in one table that references the primary key of another table. This ensures referential integrity — only values that exist in the referenced (parent) table can be stored in the referencing (child) table.

Example to create a foreign key
Reference Table (parent)
  
CREATE TABLE CUSTOMERS1(
   ID   INT,
   DEPT VARCHAR(20),
   PRIMARY KEY (ID)
);
Child Table (referencing)
  
CREATE TABLE CUSTOMERS2(
   ID      INT,
   ADDRESS VARCHAR(100),
   REFERENCES CUSTOMERS1(ID)
);
CUSTOMERS1 table (sample data):
IDDEPT
65Dairy
66Snacks
67Snacks
CUSTOMERS2 table (sample data):
IDADDRESS
65Hyderabad
66Chennai
67Hyderabad

  • The ID column in CUSTOMERS2 references CUSTOMERS1(ID). This means every ID inserted into CUSTOMERS2 must already exist in CUSTOMERS1.
  • If a user tries to insert an ID into CUSTOMERS2 that does not exist in CUSTOMERS1, the database will raise an error (referential integrity violation).
  • Thus, a foreign key creates a link between parent and child tables: you cannot reference a non-existent parent row from the child table.

Why Foreign Key
Why use foreign key

Notes:

  • The column (or columns) used as a foreign key in the child table must reference a candidate key (commonly the primary key) in the parent table.
  • The data type and structure of the referencing column(s) must match the referenced column(s).
  • The table containing the foreign key is called the child table, and the table containing the referenced key is called the parent (or referenced) table.

Why use a foreign key?

  • To prevent operations that would break links between tables (for example inserting a child row that points to a non-existent parent).
  • To prevent invalid data from being inserted into the child table by restricting values to those already present in the parent table.

Footer Content | CrackEase
→
←