Main Purpose of FOREIGN KEY

Question

When I did my first interview, I faced this confusing question. By using join we can join the tables, but what is the main purpose of FOREIGN KEY in mysql?

Please suggest me the best answer.

Answer 1

The primary purpose of FOREIGN KEYs is to maintain Referential Integrity (RI). Check out any of the standard textbooks (Connolly, Elmasri, Garcia-Molina or Silberschatz amongst others).

This fundamental concept endures despite users' and programmers' best efforts to produce systems which render the concept null and void - by that I mean that they frequently try and subvert these (reasonable) requirements of a decent database system. See here for further details on that matter.

As explained in the Wiki, RI is

a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute (column) in a different (or the same) relation (table).

To give a simple example, that means that if you have a purchase order system with an order_header table with fields as follows:

CREATE TABLE order_header
(
  order_id INTEGER,
  client_id INTEGER,
  order_date DATE,
  <...other fields depending on your individual requirements...>
);

You might then have a client table like this

CREATE TABLE client
(
  client_id INTEGER,
  client_name VARCHAR(50),
  client_address VARCHAR(50),
  <.... other fields depending on your requirements...>
);

You would then add a CONSTRAINT to your order_header table with something like this:

ALTER TABLE order_header
    ADD CONSTRAINT fk_header_client 
    FOREIGN KEY (client_id) 
    REFERENCES client (client_id);  
-- the exact syntax may differ per RDBMS.

This is telling the RDBMS that on the client_id column of the order_header table, there is now a constraint such that any client_id in order_header must already exist in the client table.

The referenced FOREIGN KEY columns(s) on the parent table need to have a UNIQUE constraint (or be the PRIMARY KEY).

Otherwise, it wouldn't make much sense - if there were 2 clients with the same client_id, how would the system be able to tell them apart - what address do you send the order to?

Note also that with many systems, you can create the CONSTRAINT in the CREATE TABLE statement.

Now, your system will be unable to create a purchase order for a client which does not exist. This doesn't mean that the wrong client can't be entered into the system, but it does help to ensure that the system is internally consistent.

Many optimizers can also take advantage of some optimizations when there are FOREIGN KEY constraints on the joined tables and produce better (more efficient) and simpler query plans.

Finally, a further advantage of using FOREIGN KEYs is that some systems also can (depending on your RDBMS) automatically create INDEXes on the FOREIGN KEY columns.

Answer 2

A foreign key means "ensure the values in this column exist in another column"

For example

create table sales_orders (
  order_id int primary key,
  customer_id int,
  ...
);

If customer_id does not have a foreign key (to table customers), a user could enter a value that does not point to a real customer. But with a foreign key, the value in customer_id would have to point to a real customer.

A foreign key must point to a unique key or a primary key, and can point to another table or its own table.

A column with a foreign key can be null.

A foreign key can also span more than one column. It has to point to the same number of columns.

Also, you can join tables that do not have foreign keys between them!

Answer 3

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.
check this page please.
Of course we can join two tables, but JOIN will not ensure referential integrity.

Answer 4

A foreign key ensures that a row in a primary table is not deleted if it's referenced by other tables and therefore it guarantees data integrity. See https://technet.microsoft.com/en-us/library/ms175464(v=sql.105).aspx