Explain the concept of foreign keys with examples(DBMS)?

Key is a data item which is used to identify a record or a value used to identify a record in a database is called a key. It helps uniquely to identify an entity from an entity set.

Key allows us to identify a set of attributes that make them sufficient to distinguish entities from each other.

Example

Account number, employee number, customer number are used as a key field because they specifically identify a record stored in a database.

Foreign Key

In a relation, the column whose data values correspond to the values of a key column in another relation is called a foreign key. In a relational database, the foreign key of a relation may be the primary key of another relation.

Example

Referential constraints are applied by primary key and foreign key relationship.

Foreign key creates a parent-child relationship between two tables.

The table having the primary key is called the Parent table.
Table having foreign key is called Child table.

Example

Consider two tables as given below for the student and marks.

Here,

Foreign key of a child refers to the Primary key of the parent table.
Foreign key of the mark table is StdID.
Primary key of the student table is StdID.
StdID of the marks table refers to the stdID of the student table.
We do not maintain all the attributes of students in the marks table because of redundancy.
When the marks table requires any information from the student table then it gets so by referring to its foreign key.

Referential constraints rules

The referential constraints rules are as follows −

We cannot delete a record from the student table (parent table) if that record exists in the marks table (child table).
We cannot insert a record into the child table if that record does not exist in the Parent table.
We cannot change the primary key value in the parent table if that record exists in the child table. But we can insert a NULL value in foreign key, specifying that the record is not related.