Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What are MySQL foreign keys?

Tags:

mysql

In an answer on Stack Overflow, I saw this code:

CREATE TABLE Favorites (
    user_id INT NOT NULL,
    movie_id INT NOT NULL,
    PRIMARY KEY (user_id, movie_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (movie_id) REFERENCES Movies(movie_id)
);

I've never used the 'foreign key' relationship keyword before.

  • What is it?
  • Why do people use it?
  • Does it provide any benefit apart from semantics?
like image 819
Delan Azabani Avatar asked Sep 15 '10 22:09

Delan Azabani


People also ask

What is the use of foreign key in MySQL with example?

The foreign key is used to link one or more than one table together. It is also known as the referencing key. A foreign key matches the primary key field of another table. It means a foreign key field in one table refers to the primary key field of the other table.

What are foreign keys used for?

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table.

What is meant by foreign keys?

A foreign key is a column or columns of data in one table that refers to the unique data values -- often the primary key data -- in another table. Foreign keys link together two or more tables in a relational database.

What is difference between primary key and foreign key in MySQL?

A primary key generally focuses on the uniqueness of the table. It assures the value in the specific column is unique. A foreign key is generally used to build a relationship between the two tables. Table allows only one primary key.


1 Answers

A foreign key is a reference to a primary key in another table or the table itself. It is used for what is called referential integrity. Basically in your table provided, for a Record to be inserted into Favorites - you would have to supply a valid user_id from the Users table, and a valid movie_id from the Movies table. With Foreign keys enforces, I could not delete a record from Users or Movies. If I didn't have foreign keys, I could delete those records. Then if I did a SELECT ... JOIN on Favorites it would break.

See Wikipedia.

like image 139
Daniel A. White Avatar answered Oct 14 '22 01:10

Daniel A. White