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:
And my external table has the following:
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
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
Advice and suggestions are appreciated,
Best How To :
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.