Wednesday , November 13 2019
Home / SQL / Definition of FOREIGN KEY constraint

Definition of FOREIGN KEY constraint

The FOREIGN KEY constraint is used to enforce referential integrity in the oracle database. The basic concept about FOREIGN KEY constraint is that “the values of a table (parent table) must store in another table (child table)”.

By the FOREIGN KEY constraint, we can build relationship between two or more tables.

  • The referenced table is known as Parent Table. The parent table is defined by setting PRIMARY KEY constraint. So, the parent table is a table where PRIMARY KEY constraint has been set.
  • The table where FOREIGN KEY constraint has been defined is called Child Table.

The FOREIGN KEY column always use  reference of parent table column on which PRIMARY KEY constraint is set.

Finally we can say that -before inserting or updating data, the values of  FOREIGN KEY column validates the value existence in the PRIMARY KEY column.

When you want to check before inserting or updating data in the child table that whether the values exist in the parent table or not, then you have to must use FOREIGN KEY constraint  in the child table. If you attempt to insert or update  in the child table by any value that are not in parent table, then the system will not perform the transaction and generate a system error.

Note : A FOREIGN KEY is used as a link  with the matching column of parent table to build up a relationship between two or more tables. So, this FOREIGN KEY constraint also can be specified as Parent & Child relationship.

Example:

Let us examine the example below. Here we are focusing on two tables, one is REGISTRATION table which is parent table and another is MARKS table which is child table.

The records of REGISTRATION table:

RegistrationNo (pk) StudentName Subject PhoneNo
0521 Md. Abdul Gani CSE 01711454648
0522 Md. Abul Hasan CSA 01812546585
0533 Md. Jabbar Ali MCSE 01745632352

In the REGISTRATION table, REGISTRATIONNO column has been specified by PRIMARY KEY constraint. So, the REGISTRATION table is parent table also known as referenced table.

The records of MARKS table:

RegistrationNo (fk) ExamName TotalMark OutOf
0521 WRITTEN 82 100
0521 VIVA 41 50
0522 WRITTEN 87 100
0522 VIVA 46 50
0533 WRITTEN 77 100

In the MARKS table, REGISTRATIONNO column has been defined as FOREIGN KEY constraint. So, the MARKS table is child table.

Here, a parent & child relationship has been built up between REGISTRATION (parent) and MARKS (child) table. So, FOREIGN KEY column of MARKS table points to the matching values of the PRIMARY KEY column of REGISTRATION table.

When you attempt to insert or update data in the REGISTRATIONNO column of MARKS table (child table), then FOREIGN KEY will validate the data whether that matching data is exist in the REGISTRATIONNO column  of REGISTRATION table (parent table). If it finds the matching data in the REGISTRATIONNO column of  parent table REGISTRATION, then data will be successfully  inserted or updated in the REGISTRATIONNO column  of child table MARKS. Otherwise the system will generate error and will not complete the transaction.

So, in conclusion we can say that FOREIGN KEY constraint prevents unmatched data (the data which is not exist in parent table)to be inserted or updated in the child table.

Note: The FOREIGN KEY constraint can be set by either CREATE TABLE statement or ALTER TABLE statement.

Syntax for CREATE TABLE statement:

CREATE   TABLE   table_name

(

column_name               datatype(length)     REFERENCES   parent_table_name (primary_key_column_name)

);

Here,

REFERENCES   keyword is used to point to the column of  parent table.

parent_table_name is the name of referenced table (parent table) where PRIMARY KEY constraint is defined.

primary_key_column_name is the name of the column of parent table on which PRIMARY KEY has been set.

Syntax for ALTER TABLE statement:

ALTER TABLE    child_table_name   ADD   CONSTRAINT   constraint_name    FOREIGN KEY  (foreign_key_column_name) REFERENCES   parent_table_name   (primary_key_column_name);

Here,

FOREIGN KEY keyword is used to define a column as FOREIGN KEY column or link column .

constraint_name  is the name of FOREIGN KEY constraint can be started with fk_ .

child_table_name    is the name of child table where FOREIGN KEY will be defined.

foreign_key_column_name is the name of column of child table on which FOREIGN KEY will be set.

Check Also

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

Set NOT NULL constraint by CREATE TABLE statement in Oracle

Definition of NOT NULL constraint: The NOT NULL constraint is used to restrict null values …

Leave a Reply

Your email address will not be published. Required fields are marked *