What is a proper naming convention for MySQL FKs?

Question

Being that they must be unique, what should I name FK's in a MySQL DB?

Answer 1

In MySQL, there is no need to give a symbolic name to foreign key constraints. If a name is not given, InnoDB creates a unique name automatically.

In any case, this is the convention that I use:

fk_[referencing table name]_[referenced table name]_[referencing field name] 

Example:

CREATE TABLE users(     user_id    int,     name       varchar(100) );  CREATE TABLE messages(     message_id int,     user_id    int );  ALTER TABLE messages ADD CONSTRAINT fk_messages_users_user_id      FOREIGN KEY (user_id) REFERENCES users(user_id); 

I try to stick with the same field names in referencing and referenced tables, as in user_id in the above example. When this is not practical, I also append the referenced field name to the foreign key name.

This naming convention allows me to "guess" the symbolic name just by looking at the table definitions, and in addition it also guarantees unique names.

Answer 2

my choice is different. in my opinion, a table should have an id field, not a user_id one, because table is just called user, so:

CREATE TABLE users(    id    int,    name       varchar(100) );  CREATE TABLE messages(    id int,    user_id    int ); 

user_id in messages table is a fk field so it has to make clear which id is (user_id).

a fully-self-explaining naming convention, in my opinion, could be:

fk_[referencing table name]_[referencing field name]_[referenced table name]_[referenced field name]  i.e.: `fk_messages_user_id_users_id` 

note:

• you can, in some case, omit the second element ([referencing field name])

• this fk could is unique, because if a messages_user table exists, the referencing field name should be user_id (and not just id) and the fk name should be:

  fk_messages_user_user_id_users_id

in other words, a foreign key naming convention make you sure about unique names if you also use a "referencing/referenced field" naming convention (and you can choose your own, of course).