mysql - Can I use the same foreign key constraint in two different tables?

Question

I am trying to create a database for work. I have two different types of users: internal and external. Each type has different properties so I just created two separate tables for them. In my internal table I have the following fields:

f_name VARCHAR(32),
l_name VARCHAR(32),
empl_status_id INT,
admin_grp_id INT,
reliab_status_id INT,
supv_id INT

And my external table has the following:

f_name VARCHAR(32),
l_name VARCHAR(32),
email VARCHAR(32),
phone VARCHAR(20),
org_id INT,
supv_id INT

I realize that I could probably create a separate table that contains the names of the users and a foreign key pointing to either internal or external, but that aside, I added a foreign key constraint to my internal table that refers supv_id to another internal user. I called it fk_supv_id. When I tried to do the same for my external user table, I got ERROR 1005 (HY000).

At first I couldn't figure out what the problem was but when I tried doing it with a different name, it worked (i.e. instead of calling it fk_supv_id just like for internal, I called it fk_xtsupv_id).

So my question is, what is the correct way to this? It's the same foreign key in two different tables. In both cases it refers to an internal user. Is there a way to do this without having two different names? Or should I opt for the table of names idea and add the supv_id constraint to that table along with f_name, l_name, and user_type?

Advice and suggestions are appreciated,

Thanks! :)

Answer 1

There are columns and there are foreign keys (FKs) and there are constraints.

You can have a column name in a table regardless of other tables.

A FK is a referencing table and column set and a referenced table and column set. All the names together identify a FK. It's a conceptual thing about a database.

A FK constraint, being a constraint, is a thing whose name must be unique over the database. It has and enforces an associated FK, namely the one described in its declaration. You can have multiple FK constraints in a table enforcing the same FK.

The DBMS has automatic unique names for FK constraints. Eg a name part plus a number part where the constraint is the numberth FK constraint of the table with that name. You can actually have the same nameless FK constraint definition text multiple times in a table and in multiple tables, each for a different FK constraint. (The ones inside a given table enforce the same FK.)

You should have a unique naming scheme for when you want to name them. Referencing and referenced table names should be involved, and when necessary distinguishing column names.

Confusingly, we say FK when we mean FK constraint.

When you say "It's the same foreign key in two different tables," that misuses terms. There are two different FKs involved, and corresponding FK constraints. You mean maybe "it's the same referencing columns and referenced table and columns in both FK constraints" or "it's the same text re referencing in both table declarations' FK constraint declarations". But the FK constraint names must be unique.

When you say "In both cases it refers to an internal user," you are confirming that the type and/or table of the referenced column are the same for both FK constraints. But they are different FK constraints for different FKs.