What is the main use of FOREIGN KEY?

Questions by ramesh_etta

Editorial / Best Answer

Answered by: MANAS KUMAR ROUT ( USA ) (TCS) (Eli LILLY ) - PFIZ

  • Jul 6th, 2007

Hey Ramesh,

I understand your interviewer's enthusiasm to know about the Foreign Key.  See foreign key definitions says its states the relationship of one attribute(Column) of one table to the corresponding column on the other table.

So one os a parent and other is child. PARENT is that table which holds the primary key and CHILD is table which holds the referential key. Hence every entry in the PRIMARY table is unique where as same entry might be repeating in the child table. BUT that same has to be present in the PARENT table (Primary KEY).

Now Going by definition Foreign key can be of two types :
========================================
1.  ON DELETE SET NULL
2.  ON DELETE SET CASCADE

DETAILS:
=======
On delete set null :
=======
When a foreign key is created by on delete set null definition then when you delete one row from the primary column (of parent table ) , then the corresponding entry in the foreign key table (Child table ) have the value "NULL" for that particular column.


ON Delete Set Cascade:
================
When the foreign key is created by this definition then when you delete the primary column (any one row -unique data) , then the Child table forcefully deletes all the rows in the child table having same value for that particular column.


I hope u got the crux of the Foreign key
Thanks
Manas Kumar Rout
SAS Consultant
Oracle Certified Associate
Unix Consultant

Showing Answers 1 - 22 of 22 Answers

vedpathak1

  • Nov 8th, 2006
 

dear ,

foreign key is used to make the relation of one table to another table ,while updating the table both will affect at the same.

  Was this answer useful?  Yes

vishwa

  • Nov 15th, 2006
 

Basically foreign key used to make a relation ship between the tables, used to reduce the redundancy of data and data integrity.

  Was this answer useful?  Yes

Sudeep Biswas

  • Nov 21st, 2006
 

Foreign key is usually the primary key of other data base table and is typically used for making joins 

  Was this answer useful?  Yes

Deepak kumar Mahapatra

  • Nov 21st, 2006
 

The main use for foreign key is to support the feature of referencial integrity.

  Was this answer useful?  Yes

DEEPAK MAHAPATRA TCS KOCHI INDIA

  • Nov 21st, 2006
 

HII DEAR

IT IS TYPE OF CONSTRAINT WHICH IS HELPFUL TO MAKE A RELATIONSHIP BETWEEN TWO OR MORE THAN TWO TABLE USING A COMMON ATTRIBUTES.

THANX

DEEPAK MAHAPATRA(KOCHI TCS)

TANVEER

  • Nov 22nd, 2006
 

FOREIGN KEY is used to maintain column values in transaction table for uniformity of data in relation to  (Primary Key) of Master Table....

Murali Krishna

  • Nov 22nd, 2006
 

In RDBMS, forign key can give a meaning for R.

  Was this answer useful?  Yes

Manas Rout

  • Nov 23rd, 2006
 

Hi,

Foreignkey are referential integrity constraints which ensures a data is correct in the child table by referring to the parent table. Mean to say if i have a table called Department (PKEY = DEPT#) and a child table called Employee (PKEY = EMP#). Also this Employee table has a column "Department#" but a NULLABLE column. So if i put a referential relation between both table i would be only allowed to enter the departments in the employee table for which there will be an entry in the Department table.

I think i am able to explain. Please let me know.

Manas

TCS, USA

Ramesh

  • Nov 24th, 2006
 

Sorry to say this that , once i have been asked this question in an interview and i had explained all this what u had did. And even the interviewer wants something more. He is specificaly asking about the main use of ForeignKey. If u can plese. Thanking u.

  Was this answer useful?  Yes

brave

  • Dec 4th, 2006
 

Foreign Key is a column in a table that does not uniquely identify rows in that table, but is used as a link to matching columns in other tables.

  Was this answer useful?  Yes

Rajneesh Kaur

  • Dec 20th, 2006
 

A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables.

  Was this answer useful?  Yes

Vipan Thakur

  • Dec 21st, 2006
 

Foreign key is used for cross-referencing and when u delete from parent table data it will automaticaly delete from child table, foreign key can accept null values....if you use "cascade" clause while deleting the parent table then it will create no effect on child table

  Was this answer useful?  Yes

abc

  • Feb 14th, 2007
 

Those who are wishing to go abroad (forigen) make use a forign Key....................................

  Was this answer useful?  Yes

Lavanya Chowdary

  • May 6th, 2007
 

It is used to make the relation between one or more tables. If the master table is having any unique key or primary key then only we can give this constraint on the detail table.

  Was this answer useful?  Yes

MANAS KUMAR ROUT ( USA ) (TCS) (Eli LILLY ) - PFIZ

  • Jul 5th, 2007
 

Hey Ramesh,

I understand your interviewer's enthusiasm to know about the Foreign Key.? See foreign key definitions says its states the relationship of one attribute(Column) of one table to the corresponding column on the other table.

So one os a parent and other is child. PARENT is that table which holds the primary key and CHILD is table which holds the referential key. Hence every entry in the PRIMARY table is unique where as same entry might be repeating in the child table. BUT that same has to be present in the PARENT table (Primary KEY).

Now Going by definition Foreign key can be of two types :
========================================
1.? ON DELETE SET NULL
2.? ON DELETE SET CASCADE

DETAILS:
=======
On delete set null :
=======
When a foreign key is created by on delete set null definition then when you delete one row from the primary?column (of parent table ) , then the corresponding entry in the foreign key table (Child table ) have the value "NULL" for that particular column.


ON Delete Set Cascade:
================
When the foreign key is created by this definition then when you delete the primary column (any one row -unique data) , then the Child table forcefully deletes all the rows in the child table having same value for that particular column.


I hope u got the crux of the Foreign key
Thanks
Manas Kumar Rout
SAS Consultant
Oracle Certified Associate
Unix Consultant

chrissen

  • Dec 23rd, 2009
 

The main use is to avoid unacceptable values.
Consider a college office record and its library, here library will have as its entry 'studentname' for only those who are in the office record, when trying to enter a name that is not in office record would result violence of referencial constrain (foreign key).

  Was this answer useful?  Yes

vishnu1122

  • Mar 4th, 2010
 

FK identifies a column(s) in one table that refers to a column(s)[Primary key columns] in another table. This way of referencing is necessary to link information between tables. Because we had split the tables based on Normalization concept and we need to maintain data integrity across all the tables.

  Was this answer useful?  Yes

prasadraja07

  • Aug 5th, 2010
 

I also want to say something about foreign key.
It is used to join two table.
Main use is if
you delete a record in parent table. It will affect child table also.
Once
you insert the record in parent table then only we can insert a record in child table.


If the answer is useful please vote me
Regards
Prasad



  Was this answer useful?  Yes

yuvaevergreen

  • Jun 14th, 2011
 

Main use of foreign key is to maintain referential integrity in Teradata. 

One cannot have a table row with a non-null value for a referencing (FK) column there is no equal value in its referenced (PK) column. Or, positively, you may designate columns in a referencing table that are FKs in other referenced tables. Because of this rule, a referenced column must be a Primary Key or must be Unique and Not Null. So when inserts are made into a referencing table in a non-null FK column, RI will check to be sure that the referenced table has a row with that same value as its Primary Key. Several views in the DDD provide information on how RI is used in the database. They all have "RI" in their names. The Parent Table is the Referenced table and the Child Table is the Referencing table.

 

  Was this answer useful?  Yes

shweta dhumal

  • Sep 8th, 2011
 

The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table.

This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

  Was this answer useful?  Yes

shweta dhumal

  • Sep 8th, 2011
 

foreign key is primary key of other table.

for example:

'STUDENT' is one table which stores information about students and 'SID' is primary key in that table and 'MARKS' is another table which stores information of student marks and that 'SID' attribute can be use in 'MARKS' table by making it foreign key of same table.

Because of that we can identify student who is in 'STUDENT' table from 'MARKS' by 'SID'

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions