Should I Use Foreign Keys

Should I really use foreign keys?

"All related tables" is not always clear, so it's not an obvious situation. There can be tables that have a common column but may never see each other.

It's handy, however, to prevent errors that slip past your primary defenses and allow data to be entered that's not easily traceable.

They don't help make queries more efficient if you have the proper indexes in place, and a good application will filter input enough that they should never be needed. But mistakes happen, and they are a cheap line of defense.

If you're just getting comfortable with designing databases, they aren't something to spend a lot of time worrying about and refining in great detail, once you have the basic parent/child relationships in place.

Here's some background--

What's wrong with foreign keys?

Do you absolutely need foreign keys in a database?

If you don't care about referential integrity then you are right. But.... you should care about referential integrity.

The problem is that people make mistakes. Computers do not.

Regarding your comment:

but say for example, the programmers do a good job of
preserving data integrity

Someone will eventually make a mistake. No one is perfect. Also if you bring someone new in you aren't always sure of their ability to write "perfect" code.

In addition to that you lose the ability to do cascading deletes and a number of other features that having defined foreign keys allow.

Are foreign keys really necessary in a database design?

Foreign keys help enforce referential integrity at the data level. They also improve performance because they're normally indexed by default.

Why do we need to use Foreign Keys?

When you use foreign keys you get:

1. Data integrity
2. faster queries.

users:

user id:

• 1
• 2
• 3

Comments:
user:

• 1
• 2
• 4 XXX invalid as 4 isn't in the users table.

Read Wikipedia please for more details about Data integrity

Why use Foreign Key constraints in MySQL?

Foreign keys enforce referential integrity. These constraints guarantee that a row in a table order_details with a field order_id referencing an orders table will never have an order_id value that doesn't exist in the orders table.

Foreign keys aren't required to have a working relational database (in fact MySQL's default storage engine doesn't support FKs), but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity). The ability to enforce referential integrity at the database level is required for the C in ACID to stand.

As for your concerns regarding performance, in general there's a performance cost, but will probably be negligible. I suggest putting in all your foreign key constraints, and only experiment without them if you have real performance issues that you cannot solve otherwise.

Should I create a Joint table or add foreign key

It's unclear what DBMS you're using, but personally I would prefer using foreign keys because many DBMS and other related products has built in support related to foreign keys. Also, a joint/mapping table will add redundancy of data, compared to using foreign keys.

Can someone explain what a Foreign Key is, and why you use it?

In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.

This takes us to Primary key. The customers table contains a unique key on each row called customerNumber this is the primary key for the table. On the orders table we have the orderNumber column which is the primary key for that table.

The orders table has a foreign key link back to the customers table though the customer Number. We call the customer Number the foreign key.

Customer Table:

customerNumber CustomerName.
       1           Bob
       2           Jane

Order table:

OrderNumber customerNumber   Status
       1           1         Shipped
       2           1         Exploded

Using the data above if you wanted to see what orders bob had you would take the primary key being bobs customer id and check the order table for all rows containing the it. This is the foreign key linking two tables.

---

Related Topics