Why should I use the foreign key if I can use WHERE?

A beginners' question about foreign key in MySQL.

In w3school 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?

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

As a constraint on the table to stop you inserting something that doesn't point to anything;
As a clue for the optimizer; and
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).

Why should I create a foreign key constraint?

I am designing a oracle database for Timesheet application. I am wondering if it is really necessary to have the foreign key constraints on tables (Master and Child). As we know that theoretically it is good to have a proper referential integrity on

Why should I include the primary key column in this insert statement?

Given a SQL Server Express Compact Edition database with a table called "Clients" with the following columns: ID, FirstName, MiddleName, Nickname, LastName, Suffix, IsMale where ID is an auto-incrementing primary key / identity value, when I try

How to write a query that should send me the foreign key only if it meets all the conditions

I have Parent table A as **SID name** 1 ABC 2 XYZ 3 ZYK ......... and child table B as SID Status 1 New 1 Open 1 Closed 2 Open 2 Open 3 New 3 Open 3 Closed 3 REJ Now my question is i want to fire query against the child table, and want to find out on

errno: 150 & ldquo; The foreign key constraint is incorrectly formed & rdquo;

I'm getting the 150 error when attempting to run the following setup script. Whats wrong? serverPermissions seems to trigger the error but I'm not exactly sure why. I think the foreign key constraints are all properly mapped as far as I can tell. I a

why hibernate do not set the foreign key in the owner side of OneToMany

I have 2 Entities, Hospital and Department, Department reference its hospital by hospital_id in it. @Entity public class Hospital { ... private Set<Department> departments; @OneToMany(mappedBy = "hospital", cascade = CascadeType.ALL) publi

Why should I use Web services between the Android app and the MySQL database (remotely)?

I came across suggestions of using web services between android app and both. But here are my concerns Cons: 1) More development time of web services 2) Securing it. 3) Performace - Won't the web services become an additional layer in between and con

Add a single multi-column constraint on the foreign key using fluid-nhibernate automation

I'm an NHibernate and fluent-nhibernate newbie. And I've got some problem with unique constraint and nhibernate mapping. I've got the following part of domain model. public class Batch { public virtual int Id {get; set;} public virtual string Name {g

How to choose the name of the Foreign Key column using Sequelize and mySql?

I am using sequelize to model a mySql-database schema in my node-application. An extract of my model looks like this: I have a company-table and a department-table. A company can have multiple departments and a department belongs to only one company.

Using JPA annotations with Hibernate to describe the @OneToMany relationship where the foreign key is only in the child table

I have an existing data model that I'm very happy with: public class Garden { private String name; // "Oak Grove" private List<Plant> plants; } public class Plant { private String name; // "Cherry Tomato" } I would like to map th

PostgreSQL using constants in the foreign key

The syntax for Foreign Key, as described in official documentation, includes set of column from table A referencing set of column from table B. Is it possible to use Constant in foreign key declaration? The problem is, I have table DICTIONARIES which

Entity Framework: I configure the foreign key, SaveChanges then accesses the navigation property, but it does not load the entity concerned. Why not?

I am using this Entity class with Entity Framework 5 Code First: public class Survey { [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int ID { get; set; } public string SurveyName { get; set; } [Required] public int ClientID { get; set;

Automatically delete MySQL records for labels from favorites with a middle table by connecting them using the foreign keys?

My MySQL app allows users to create bookmarks and organize them with tags. A tag is added to a bookmark with a middle MySQL DB table bookmark_tag_relationship I would like to add a Foreign Key to auto-delete all records in bookmark_tag_relationship w

Display additional fields in addition to the foreign key Id in a list by using Linq to sql

What is the best way to display the username instead of the Author_Id (foreign Key) when I display a list of records. I am using Linq to Sql on this project. <% foreach (var item in Model) { %> <tr> <td> <%= Html.Encode(item.Title) %&

Fluent nhibernate One-to-Many when Inverse is used The foreign key of the child table is null

I have a parent and child table and entites are created and mapped using one to many relation ship. On one to many side when i use Inverse() then the child table's foreign key value is inserted as null. public class TableA { public virtual long ID {

Related Articles