In my interview, I was asked "what is the use of foreign key"?
My answer was:
"It is used to reference the primary key of next table that can be used to join the table".
And still, he said "you are not at the point". I tried google but still I can not find.
So what can be the answer?
A foreign key is a specific kind of constraint.
A constraint is a rule that restricts the values in a database.
Please follow this link to find detailed answer (It's for Oracle, not MySql, but the foreign key works in the same way in all databases):
http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm
Use a constraint to define an integrity constraint--a rule that restricts the values in a database. Oracle Database lets you create six types of constraints and lets you declare them in two ways.
The six types of integrity constraint are described briefly here and more fully in "Semantics":
A NOT NULL constraint prohibits a database value from being null.
A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
A foreign key constraint requires values in one table to match values in another table.
A check constraint requires a value in the database to comply with a specified condition.
A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.
As you see from the definition - the foreign key is a constraint, that works in such a way, that it simply checks if a given value in a column of one table exists in a given column of another table.
It is interesting that you tagged mysql here, because the default MyISAM engine in MySQL does not maintain referential integrity. In other words, it will actually allow you to create a foreign key, but it does not enforce anything (See here).
So if Table2.ColumnB had a foreign key to Table1.ColumnA, and you tried to add a value to Table2.ColumnB that did not exist in Table1.ColumnA, it would let you. It would not enforce the constraint for you. Other MySQL engines such as InnoDB however to support these constraints.
All of my early database work was in MySQL using a MyISAM engine. All foreign key constraints had to be enforced at the programming level since the database would not do it for me. This led to some problems when I started working with SQL Server because I was not properly setting up foreign key constraints.
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Suppose for example you have two tables, table one is having these columns, pid,fname,lname where pid is the primary key for the table one. Whereas, Table two is having these columns, nid, value, pid, here nid is primary key for table two and pid will act as foreign key for table two. This pid will act as link between two given tables. With the help of pid in table two, we can fetch the value data from table two for fname or lname of table one.
