我可以在两个不同的表中使用相同的外键约束吗？ [英] Can I use the same foreign key constraint in two different tables?

问题描述

我想为工作创建一个数据库。我有两种不同类型的用户：内部和外部。每种类型都有不同的属性，所以我刚刚为他们创建了两个单独的表。在我的内部表中有以下字段：

  f_name VARCHAR（32），
 l_name VARCHAR 
 empl_status_id INT，
 admin_grp_id INT，
 reliab_status_id INT，
 supv_id INT 
  

b $ b

而我的外部表有以下：

  f_name VARCHAR（32），
 l_name VARCHAR （32），
 email VARCHAR（32），
 phone VARCHAR（20），
 org_id INT，
 supv_id INT 
  pre> 
 
 
我意识到我可能创建一个单独的表，包含用户的名称和外键指向内部或外部，但除此之外，我添加一个外键约束到我的内部表，引用 supv_id 给另一个内部用户。我叫它 fk_supv_id 。当我试图对我的外部用户表做同样的事情，我得到 ERROR 1005（HY000）。 
 
 
 
开始我不知道这是什么问题，但是当我尝试使用不同的名称，它工作（即，而不是调用 fk_supv_id 就像内部，我调用它 fk_xtsupv_id ）。 
 
 
 
所以我的问题是，这是正确的方法是什么？它在两个不同的表中是相同的外键。在这两种情况下，它指的是内部用户。有没有办法做到这一点，没有两个不同的名字？或者，我应该选择表名的想法，并添加 supv_id 约束到该表以及 f_name ， l_name 和 user_type ？
 
 
 
建议和建议， 
 
 
 
谢谢！ ：）
解决方案
有列，有外键（FK），并有约束。
 
 
 
无论其他表如何，您都可以在表中有一个列名。
 
 
 
 FK是引用表和列集以及引用表和列集。所有的名字一起标识一个FK。这是一个关于数据库的概念。
 
 
 
一个FK约束是一个约束，它的名称在数据库中必须是唯一的。它具有并强制执行相关联的FK，即在其声明中描述的FK。您可以在执行相同FK的表中使用多个FK约束。
 
 
 
 DBMS对FK约束具有自动唯一名称。例如，名称部分加上数字部分，其中约束是具有该名称的表的第FK个约束。实际上，可以在表和多个表中多次使用相同的无名FK约束定义文本，每个表都有不同的FK约束。 （给定表中的那些内容强制使用相同的FK。）
 
 
 
当你想给它们命名时，你应该有一个唯一的命名方案。参考和引用的表名称，当必要时区分列名称。
 
 
 
令人困惑的是，当我们指FK约束时，我们说FK。
 
 
 
当你说这是两个不同的表中的相同的外键时，它会滥用术语。涉及两个不同的FK，以及相应的FK约束。你的意思可能是它是相同的引用列和引用的表和列在FK约束或它是相同的文本重新引用在两个表声明的FK约束声明。但是FK约束名称必须是唯一的。
 
 
 
当你说在这两种情况下都引用一个内部用户，你确认类型和/所引用的列对于两个FK约束是相同的。但是对于不同的FK，它们是不同的FK约束。
 
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! :)
解决方案
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.

                        
这篇关于我可以在两个不同的表中使用相同的外键约束吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！