How do I use foreign keys to get stuff from a database?

Question

I'm having trouble understanding how to use foreign keys in databases. I've set up a database that has some tables related by foreign keys, but I'm a bit confused about how to use them. The only way I can think of is by just using SELECT statement like:

SELECT * FROM Names WHERE fkNames = 1;

but then why did I have to add a CONSTRAINT to the fkNames column that designated it as a foreign key? What benefit does that offer over just using a normal column?

Or is there a better way that I'm just missing?

Answer 1

A foreign key is not for YOU to use. It is used by the database to enforce referential integrity.



Say you have these tables:



Table: ITEM_TYPE



ITEM_TYPE_ID ---------- NUMBER

ITEM_TYPE_DESCRIPTION ------------- CHAR(30)





Table: ITEMS



ITEM_ID ------------ NUMBER

ITEM_TYPE_ID ---------- NUMBER





And let's assume that you have a foreign key that relates ITEM_TYPE_ID in the ITEMS table to the ITEM_TYPE_ID in the ITEM_TYPE table.



What this means is that if you try to insert a row into the ITEM table with an ITEM_TYPE_ID = 315, but no row exists in the ITEM_TYPE table with ITEM_TYPE_ID = 315, then the database will reject the INSERT statement and give you an SQL error code indicating that the insert you are trying violates the foreign key constraint.



You don't use the foreign key names yourself in your SQL statements.

Answer 2

You use foreign keys for what is called referential integrity. It enforces that values placed in table A will match the values in table B.



For example, if we have table customers with customerid, name, etc. and we have table orders with orderid, customerid, and some information about the order, we do not want to put data in the table for someone we know nothing about. Where would you ship the order, who would you bill? Our solution is to put a foreign key on the orders table to link customerid to the customerid in the customers table. this ensures we have only known customers in our orders table.

Answer 3

See this example, foreign keys are used for i.e. linking customers and their orders

http://www.1keydata.com/sql/sql-foreign-key.html