Why do I need to use foreign key if I can use WHERE ?

Question

A beginners' question about foreign key in MySQL.

In w3cshool it says,

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

And also there is WHERE,

WHERE id = page_id

So if I can use WHERE for linking the tables, what is the main purpose of having foreign key?

Answer 1

Maintaining referential integrity and indexing.

Answer 2

The Foreign is used for referential integrity.

See An introduction to foreign keys and referential integrity in MySQL

Answer 3

It's not strictly needed for the query, it's true. It exists for several reasons:

1. As a constraint on the table to stop you inserting something that doesn't point to anything;
2. As a clue for the optimizer; and
3. For historical reasons where is was more needed.

(1) is probably the important one of the three. This is called referential integrity. It means that if there is a value in a foreign key there will be a corresponding record with that value as a primary key in the parent table.

That being said, not all databases support referential integrity (eg MySQL/MyISAM tables) and those that do don't necessarily enforce it (for performance reasons).

Answer 4

So if I can use WHERE for linking the tables, what is the main purpose of having foreign key?

Because WHERE clause is not limited to equijoins on foreign keys.

Say, if you have a table which describes price ranges and discounts, you use this complex condition to join the tables:

SELECT  *
FROM    Goods
JOIN    PriceRange
ON      PriceRange.Price =
        (
        SELECT  MAX(Price)
        FROM    PriceRange
        WHERE   PriceRange.Price <= Goods.Price
        )

You cannot link these table with a foreign key relationship, but you can easily join them.

See this entry in my blog for more details:

• What is entity-relationship model?

The pk-to-pk binding, though, is still important. A FOREIGN KEY can assure you that the entitie you are linking are described by your relational model.

With a FOREIGN KEY-backed design, you cannot declare a relationship to an entity whose PRIMARY KEY is absent in the table that describes that entity.

SQL Server can even take this fact into account and optimize the certain types of queries.

Say, this query:

SELECT  f.*
FROM    t_foreign f
WHERE   f.pid IN
        (
        SELECT  id
        FROM    t_primary p
        )

will not even look into t_primary if the FOREIGN KEY relationship is defined between t_foreign and t_primary.

See this article for more details:

• Constraints and the optimizer in SQL Server: FOREIGN KEY

Answer 5

A Foreign Key is used to maintain referential integrity, while a WHERE clause is used to join tables together in a SQL operation such as a select. The where clause can operate across multiple tables, but it is purely there as a filter.

Strictly speaking, you can get away without referential integrity, but it's not a good idea. Without referential integrity, you end up relying on your client application not incorrectly deleting or updating something at one end of a relationship chain that would have a knock on effect on the data, e.g. changing a key value to point to one that's not there.

Referential integrity is a great way of ensuring that related data is held in a consistent way.

Answer 6

The primary purpose of a WHERE clause is to limit the rows returned by the query. See SELECT Syntax.

Primary key/Foreign key relationships maintain referential integrity and with proper indexing increase the performance of queries. (See Pete OHanlon's explanation, above and JOIN Types)

Answer 7

First of all. Good Question !!

MySql is an RDBMS - Relational DBMS, so all the entities (tables) are related by an column.

EMPLOYEE - EMPID EMPNAME DEPTID

DEPARTMENT - DEPTID DEPTNAME

DEPTID is foriegn key in the EMPLOYEE table and primary key in the DEPARTMENT table.

This relation is imaginary relation of objects just an consideration or kind of designing for structuring data in a easy way to retrieve in future. NOT A PHYSICAL RELATION (because its a programming language)

In order to retrive that data, we need few syntax and described by the Creator of SQL.

SELECT * from EMPLOYEE

SELECT * FROM DEPARTMENT

SELECT * FROM EMPLOYEE WHERE DEPTID = 5

Here we have realted the two tables imaginary for our convinent, but for the required result we used this syntax WHERE DEPTID = 5.

Answer 8

the RESTRICT operator (WHERE) has nothing to do with referential constraints!

quote from C. J. Date's Relational Database Dictionary

foreign key Let R1 and R2 be relvars, not necessarily distinct, and let K be a key for R1. Let FK be a subset of the heading of R2 such that there exists a possibly empty sequence of attribute renamings that maps K into K' (say), where K' and FK contain exactly the same attributes. Then FK is a foreign key

referential integrity Loosely, the rule that no referencing tuple is allowed to exist if the corresponding referenced tuple doesn't exist. More precisely, let FK be some foreign key in some referencing relvar R2; let K be the corresponding key in the corresponding referenced relvar R1, and let K' be derived from K as described under foreign key. Then the referential integrity rule requires there never to be a time at which there exists an FK value in R2 that isn't the K' value for some (necessarily unique) tuple in R1 at the time in question. R1 and R2 here are the referenced relvar and the referencing relvar, respectively, and the constraint between them is a referential constraint.

Examples: In relvar SP, {S#} and {P#} are foreign keys corresponding to the keys {S#} and {P#} in relvars S and P, respectively. Note that the key in the referenced relvar that corresponds to a given foreign key is not required to be a primary key specifically.

Answer 9

I have another good reason to add the key relationships to your database. There are various code generators that use this information to generate an object model from your database. One notable pattern in common use is the ActiveRecord pattern. Without key relationships, the ActiveRecord pattern would not know how your database entities are related so it would generate a much less useful object model.

Code generation is not appropriate for every software project. But, it is helpful on a large number of projects. If you aren't using code generation you owe it to yourself to at least look into it.