Necessity of foreign key in this case(innoDB/mysql)

Question

Hi, i recently migrated my whole DB from myisam to innodb. I am fairly new to all this so i have a question regarding the use of the foreign key.

Lets say i have two tables: users, messages.

users

id (pr_key)
name

messages

id (pr_key)
user_id
message

The id fields are both auto incremented.

So now in my queries i join these 2 tables already. Is it still necessary to place a foreign key here, i actually don't see a point. Does it have performance benefits.

If i choose to put a foreign key here i assume i have to make the pr_key of messages both id, and user_id.

Will adding the other prim_key not just take more space, and resulting in slower performance.

Now if the table has two pr_keys, and i only query on one of them, will i still have the same performance benefits. Or do i need explicitly use the two keys.

I know in this example i will be searching on user_id so it is maybe smart to index this anyway. But what if the field is a field where there are no searches on. Is it still good to place a multiple primary key on this field, just for the foreign hey relation.

Thank you!

Answer 1

It has data integrity benefits.

it will stop application code from entering user_id of 1234 in message table when there is no user with id = 1234 in user table. And it will stop application from deleting user when the user has any messages in message table.

Name of the PK does not have to match the name of the FK in the referencing child table.

"If i choose to put a foreign key here i assume i have to make the pr_key of messages both id, and user_id. "

NO. You don't change the key in the messages table, you just establish a Foreign Key (FK) constraint on the User_Id column in the messages table, that references (points to) the id column on the users table. No additional PK is required in messages, and since PK is already unique (You Can't have two messages with same messageId) making the PK on both id and user_id cannot add any more uniqueness.)

Bottom line here is that you may have a fundamental misconception about what a Primary Key and a Foreign Key actually are.

• A PK is a constraint on uniqueness of a row, it requires an index.
• A FK is a constraint on the value of a column (it mandates that the value exist as a PK value in some other referenced table) It does not require an index.

@Noah, check out this Other SO question

Answer 2

Whether you establish a formal FOREIGN KEY rule on messages(user_id) or not, the simple fact is that this is a foreign key relationship.

First, remember that a proper primary key on a table should be sufficient to uniquely identify each record in the table. Considering that each message will already have that with the id field, you don't need nor do you particularly want to wrap the user_id into the primary key of the messages table.

Second, as Charles already stated declaring a FOREIGN KEY on messages(user_id) that REFERENCES users(id) will allow you to ensure the integrity of your data. Using a foreign key constraint you can specify what to do when a record in the users table is deleted that has corresponding records in the messages table. Your choices are ON DELETE CASCADE (delete all of the child records for this user record), ON DELETE RESTRICT (don't allow a user to be deleted that has records in the messages table), ON DELETE NO ACTION (ignore delete operations), ON DELETE SET NULL (preserves the child records but sets the user_id field to NULL).

Each of these options is appropriate depending on the circumstances, but the most important thing here is that you can prevent unexpected null pointers from the child table to the parent table.

Third, establishing the FOREIGN KEY relationship will provide performance gains as this will generate an INDEX on messages(user_id) that corresponds with the PRIMARY KEY on the users table. When performing any query that joins on these fields, you will see that the number of records that have to queried to return the child records is substantially reduced as compared to not having the FOREIGN KEY established.

@Charles Bretana - The question you link to is specific to Microsoft SQL Server. The question here is regarding MySQL / InnoDB.

I would recommend that you look at the documentation for InnoDB Foreign Key Constraints.

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.