mysql - Can I use the same foreign key constraint in two different tables? -
i trying create database work. have 2 different types of users: internal , external. each type has different properties created 2 separate tables them. in internal table have next 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 external table has following:
f_name varchar(32), l_name varchar(32), email varchar(32), phone varchar(20), org_id int, supv_id int
i realize create separate table contains names of users , foreign key pointing either internal or external, aside, added foreign key constraint internal table refers supv_id
internal user. called fk_supv_id
. when tried same external user table, got error 1005 (hy000)
.
at first couldn't figure out problem when tried doing different name, worked (i.e. instead of calling fk_supv_id
internal, called fk_xtsupv_id
).
so question is, right way this? it's same foreign key in 2 different tables. in both cases refers internal user. there way without having 2 different names? or should opt table of names thought , add together supv_id
constraint table along f_name
, l_name
, , user_type
?
advice , suggestions appreciated,
thanks! :)
there columns , there foreign keys (fks) , there constraints.
you can have column name in table regardless of other tables.
a fk referencing table , column set , referenced table , column set. names identify fk. it's conceptual thing database.
a fk constraint, beingness constraint, thing name must unique on database. has , enforces associated fk, namely 1 described in declaration. can have multiple fk constraints in table enforcing same fk.
the dbms has automatic unique names fk constraints. eg name part plus number part constraint numberth fk constraint of table name. can have same nameless fk constraint definition text multiple times in table , in multiple tables, each different fk constraint. (the ones within given table enforce same fk.)
you should have unique naming scheme when want name them. referencing , referenced table names should involved, , when necessary distinguishing column names.
confusingly, fk when mean fk constraint.
when "it's same foreign key in 2 different tables," misuses terms. there 2 different fks involved, , corresponding fk constraints. mean maybe "it's same referencing columns , referenced table , columns in both fk constraints" or "it's same text re referencing in both table declarations' fk constraint declarations". fk constraint names must unique.
when "in both cases refers internal user," confirming type and/or table of referenced column same both fk constraints. different fk constraints different fks.
mysql database foreign-keys foreign-key-relationship