What are the advantages of explicitly setting up relationships inside of a database.

Question

When your creating a database schema and come up with all the foreign keys. What are the advantages of explicitly defining them as such in the database?

Are there advantages?

If it's reliant MySQL is the db I will be using.

Answer 1

When you properly define primary/foreign key relationships, the database handles enforcing referential integrity. You won't be allowed to insert data that violates the restrictions you've specified at the database level.

Answer 2

Foreign key constraints are used for maintaining Referential Integrity which is a database constraint that ensures that references between data are indeed valid and intact (a database should not only store data but should also ensure its quality). In other words, they help to ensure that relationships between tables remain consistent. From Wikipedia:

Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table).

Less formally, and in relational databases: For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. Some relational database management systems (RDBMS) can enforce referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used may be determined by a referential integrity constraint defined in a data dictionary.

---

An example of a database that has not enforced referential integrity. In this example, there is a foreign key (artist_id) value in the album table that references a non-existent artist — in other words there is a foreign key value with no corresponding primary key value in the referenced table. What happened here was that there was an artist called "Aerosmith", with an artist_id of "4", which was deleted from the artist table. However, the album "Eat the Rich" referred to this artist. With referential integrity enforced, this would not have been possible.

In order to create a foreign key between two tables with MySQL, both tables need to be InnoDB tables (with the default MyISAM table type, you can "define" a foreign key but they do not actually do anything).

Answer 3

Data integrity - the constraint helps ensure that the data in different tables is consistent. A side benefit is that constraints are available to the DBMS's query optimizer which will help it to optimise the performance of some queries.