What is the use of a foreign key in DB?

Question

If possible please provide a simple example

Answer 1

Foreign keys provide a relationship between two tables of data. For example: pets & their owners.

Without a key:

Table Owners:
owner_id
owner_name

Table Pets:
pet_id
pet_name

There is no way to tell which pet belongs to which owner. Adding a foreign key to the pets table:

Pets table:
pet_id
pet_name
owner_id

Allows you to see all the information on the owner of the pet by assigning each pet to a specific owner. You can then see all the information with a query like:

SELECT * FROM owners AS o JOIN pets AS p ON o.owner_id = p.owner_id

You can effectively think of it as putting each person's driver's license # on their pet's collar. The # itself doesn't tell you anything about the owner, but you can use the # to get the information you need.

Answer 2

Use Of Foreign Key

Answer 3

To enforce foreign constraint, you do know what "foreign" relationship is in DB, yes?

Can't recall the SQL right off my head, just it is declared similarly as primary key is defined.

But other than enforcing the existence of a relationship it does nothing extra; no cardinality, no nullibility, not anything. Also the dictation of it in SQL is also optional.

Answer 4

A foreign key (fk) is used to link tables to control the duplication of data.

Example: Table - employees
Rows - employeeid, firstname, lastname, addressline1, addressline2, city, state, zip, JOBID (fk), PAYGRADEID (fk)

Table - jobs
Rows - JOBID, title, departmentid, supervisorid, subordinateid

Table - pay
Rows - PAYGRADEID, startingsalary, highestsalary

notice how the fk's in the employees table link to other tables. also, note how the table JOBS has 3 fk's linking it to other tables.
Fk's are also linked to normalizing a database.
It's good to have a certain level of non-redundance of data in a database, but too much or over normalization, can be a problem too.