Currently, I am working on the very beginnings of a user database in MySQL InnoDB. So, I will use that to help demonstrate.
目前，我正在开发MySQL InnoDB中的用户数据库。我将用它来帮助演示。
Users Roles
--------------- ---------------
userid (bigint) PK >roleid (tinyint) PK
email (varchar) rolename (varchar)
username (varchar)
password (char)
>roleid (tinyint) FK
created (timestamp)
Right now, I am trying to create a one-to-many relationship between Role and Users, based on roleid. For this, I am thinking that On UPDATE CASCADE and On DELETE Restrict.
现在，我正在尝试创建一个基于roleid的角色和用户之间的一对多关系。为此，我正在考虑更新级联和删除限制。
That's what I feel like I should do, I'm not sure if it's correct or not. However, I'd like to gain a better understanding of it.
这就是我觉得我应该做的，我不确定它是否正确。但是，我希望能更好地理解它。
Say I wanted to created one-to-one, then that would look like On UPDATE Restrict and On DELETE Restrict, is that correct?
假设我想创建一对一，那么在更新限制和删除限制上，这是正确的吗?
Sorry, I am completely confused here and I am unable to find a tutorial, blog, or explanation that breaks down the different settings to the Relational Model. Could anyone help explain it these types as well as the other types (many-to-many, many-to-one) based on what I have here?
不好意思，我在这里完全搞不懂，我找不到一个教程、博客或者解释，把不同的设置分解成关系模型。有人能根据我这里的内容来解释这些类型以及其他类型(多对多，多对一)吗?
1 个解决方案
#1
4
One role can have many users associated to that role. This entire relationship is represented physically with a foreign key on the USERS table that references the ROLES table.
一个角色可以有许多与该角色相关联的用户。整个关系用用户表上引用role表的外键表示。
The ON UPDATE and ON DELETE options in the foreign key constraint help to enforce "referential integrity" in the database, but they don't specify at all what the relationship is between the USERS and ROLES entities.
外键约束中的ON UPDATE和ON DELETE选项有助于在数据库中强制执行“引用完整性”，但它们根本不指定用户和角色实体之间的关系。
If I create this foreign key with ON DELETE RESTRICT, when I would try to delete a record from the ROLES table where the key was in use on the USERS table, i would get an error. This has nothing to do with the type of logical relationship that exists - it is just a constraint.
如果我创建这个带有ON DELETE limit的外键，当我试图从ROLES表中删除一条记录时，我就会得到一个错误。这与存在的逻辑关系类型无关——它只是一个约束。
A many to many relationship cannot be modeled using one foreign key. Logically, if A user can have Many Roles, storing the role id on the users table doesn't make sense.
许多到许多关系不能使用一个外键进行建模。逻辑上，如果一个用户可以有多个角色，那么将角色id存储在users表中是没有意义的。
In that case, you would create a table in between, and put the userid and roleid columns on this table, with foreign keys connecting them to users and roles respectively.
在这种情况下，您将在其中创建一个表，并将userid和roleid列放在该表上，并使用外键将它们分别连接到用户和角色。
USERS USERS_ROLES ROLES
userid PK - userid FK
roleid FK - roleid PK
Here's the MYSQL manual pages about foreign key constraints. It's a good reference and explains what each option means.
下面是关于外键约束的MYSQL手册页面。这是一个很好的参考，并解释了每个选项的含义。
Edit:
编辑:
This touched on the one-to-many and many-to-many relationship types. Rarely will you see a one-to-one type in a database (in those cases merging the tables makes sense). Sometimes for performance you would use it. In these cases, typically your primary key should be the same on both tables:
这涉及一对多和多对多关系类型。很少会在数据库中看到一对一的类型(在这种情况下，合并表是有意义的)。有时为了表现你会用它。在这些情况下，通常您的主键在两个表上应该是相同的:
USERS USERS_EXTENDED_ATTRIBUTES
userid PK - userid PK FK
Only 1 user id should exist on each table for a 1-1 relationship.
对于1-1关系，每个表上应该只有一个用户id。
#1
4
One role can have many users associated to that role. This entire relationship is represented physically with a foreign key on the USERS table that references the ROLES table.
一个角色可以有许多与该角色相关联的用户。整个关系用用户表上引用role表的外键表示。
The ON UPDATE and ON DELETE options in the foreign key constraint help to enforce "referential integrity" in the database, but they don't specify at all what the relationship is between the USERS and ROLES entities.
外键约束中的ON UPDATE和ON DELETE选项有助于在数据库中强制执行“引用完整性”，但它们根本不指定用户和角色实体之间的关系。
If I create this foreign key with ON DELETE RESTRICT, when I would try to delete a record from the ROLES table where the key was in use on the USERS table, i would get an error. This has nothing to do with the type of logical relationship that exists - it is just a constraint.
如果我创建这个带有ON DELETE limit的外键，当我试图从ROLES表中删除一条记录时，我就会得到一个错误。这与存在的逻辑关系类型无关——它只是一个约束。
A many to many relationship cannot be modeled using one foreign key. Logically, if A user can have Many Roles, storing the role id on the users table doesn't make sense.
许多到许多关系不能使用一个外键进行建模。逻辑上，如果一个用户可以有多个角色，那么将角色id存储在users表中是没有意义的。
In that case, you would create a table in between, and put the userid and roleid columns on this table, with foreign keys connecting them to users and roles respectively.
在这种情况下，您将在其中创建一个表，并将userid和roleid列放在该表上，并使用外键将它们分别连接到用户和角色。
USERS USERS_ROLES ROLES
userid PK - userid FK
roleid FK - roleid PK
Here's the MYSQL manual pages about foreign key constraints. It's a good reference and explains what each option means.
下面是关于外键约束的MYSQL手册页面。这是一个很好的参考，并解释了每个选项的含义。
Edit:
编辑:
This touched on the one-to-many and many-to-many relationship types. Rarely will you see a one-to-one type in a database (in those cases merging the tables makes sense). Sometimes for performance you would use it. In these cases, typically your primary key should be the same on both tables:
这涉及一对多和多对多关系类型。很少会在数据库中看到一对一的类型(在这种情况下，合并表是有意义的)。有时为了表现你会用它。在这些情况下，通常您的主键在两个表上应该是相同的:
USERS USERS_EXTENDED_ATTRIBUTES
userid PK - userid PK FK
Only 1 user id should exist on each table for a 1-1 relationship.
对于1-1关系，每个表上应该只有一个用户id。