I want to start using table relations in a new project.
我想在一个新项目中开始使用表关系。
After some googling I got 2 tables set up as InnoDB:
在谷歌上搜索了几次之后，我得到了两个作为InnoDB的表格:
The keys I want to link are
我要链接的键是
->users->userid (primary) ->sessions->userid (index)
- >用户- >用户标识(主)- >会话- >用户标识(索引)
The only thing that I don't understand in this process is what the different settings for "On update" and "On delete" do
在这个过程中，我唯一不明白的是“On update”和“On delete”的不同设置是什么意思
The options here are:
这里的选项是:
- -- (nothing?)
- ——(没有?)
- Cascade (???)
- 级联(? ? ?)
- Set Null (sets everything to null?)
- 设置Null(将所有东西设置为Null ?)
- No action (well duh...)
- 不采取行动(哦…)
- Restrict (???)
- 限制(? ? ?)
I basically want the data in sessions to be deleted when a user is completely deleted This since the sessions will only be deleted when the expiration is detected by my session manager...
我基本上希望在用户完全删除时删除会话中的数据，因为只有在会话管理器检测到过期时才会删除会话……
So if anyone can tell me what these options do it would be much appreciated.
因此，如果有人能告诉我这些选择是什么，我们将非常感激。
2 个解决方案
#1
105
CASCADE will propagate the change when the parent changes. (If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.)
当父级更改时，级联将传播更改。(如果删除行，则将删除引用该行的约束表中的行，等等)。
SET NULL sets the column value to NULL when a parent row goes away.
设置NULL，当父行消失时，将列值设置为NULL。
RESTRICT causes the attempted DELETE of a parent row to fail.
限制导致试图删除父行失败。
EDIT: You didn't ask about them, but the SQL standard defines two other actions: SET DEFAULT and NO ACTION. In MySQL, NO ACTION is equivalent to RESTRICT. (In some DBMSs, NO ACTION is a deferred check, but in MySQL all checks are immediate.) The MySQL parser accepts SET DEFAULT, but both the InnoDB and NDB engines reject those statements, so SET DEFAULT can't actually be used for either an ON UPDATE or ON DELETE constraint.
编辑:您没有询问它们，但是SQL标准定义了另外两个操作:设置默认值和不设置操作。在MySQL中，没有任何操作等同于限制。(在某些dbms中，没有操作是延迟检查，但在MySQL中，所有检查都是即时的。)MySQL解析器接受SET DEFAULT，但InnoDB和NDB引擎都拒绝这些语句，因此SET DEFAULT实际上不能用于ON UPDATE或ON DELETE约束。
Also, note that cascading foreign key actions do not activate triggers in MySQL.
另外，注意级联外键操作在MySQL中不会激活触发器。
#2
13
The table containing the foreign key is called the referencing or child table, and the table containing the candidate key is called the referenced or parent table.
包含外键的表称为引用表或子表，包含候选键的表称为引用表或父表。
Set NULL : Sets the column value to NULL when you delete the parent table row.
设置NULL:在删除父表行时，将列值设置为NULL。
CASCADE : CASCADE will propagate the change when the parent changes. If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.
级联:级联将在父级更改时传播更改。如果删除一行，引用该行的受约束表中的行也将被删除，等等。
RESTRICT : RESTRICT causes you can not delete a given parent row if a child row exists that references the value for that parent row.
限制:如果存在引用父行值的子行，则无法删除给定父行。
NO ACTION : NO ACTION and RESTRICT are very much alike. when an UPDATE or DELETE statement is executed on the referenced table, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. in short child row no concern if parent row delete or update.
没有行动:没有行动和限制是非常相似的。当更新或删除语句在引用的表上执行时，DBMS在语句执行的末尾验证没有任何引用关系被违反。在短子行中，如果父行删除或更新，则不必担心。
#1
105
CASCADE will propagate the change when the parent changes. (If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.)
当父级更改时，级联将传播更改。(如果删除行，则将删除引用该行的约束表中的行，等等)。
SET NULL sets the column value to NULL when a parent row goes away.
设置NULL，当父行消失时，将列值设置为NULL。
RESTRICT causes the attempted DELETE of a parent row to fail.
限制导致试图删除父行失败。
EDIT: You didn't ask about them, but the SQL standard defines two other actions: SET DEFAULT and NO ACTION. In MySQL, NO ACTION is equivalent to RESTRICT. (In some DBMSs, NO ACTION is a deferred check, but in MySQL all checks are immediate.) The MySQL parser accepts SET DEFAULT, but both the InnoDB and NDB engines reject those statements, so SET DEFAULT can't actually be used for either an ON UPDATE or ON DELETE constraint.
编辑:您没有询问它们，但是SQL标准定义了另外两个操作:设置默认值和不设置操作。在MySQL中，没有任何操作等同于限制。(在某些dbms中，没有操作是延迟检查，但在MySQL中，所有检查都是即时的。)MySQL解析器接受SET DEFAULT，但InnoDB和NDB引擎都拒绝这些语句，因此SET DEFAULT实际上不能用于ON UPDATE或ON DELETE约束。
Also, note that cascading foreign key actions do not activate triggers in MySQL.
另外，注意级联外键操作在MySQL中不会激活触发器。
#2
13
The table containing the foreign key is called the referencing or child table, and the table containing the candidate key is called the referenced or parent table.
包含外键的表称为引用表或子表，包含候选键的表称为引用表或父表。
Set NULL : Sets the column value to NULL when you delete the parent table row.
设置NULL:在删除父表行时，将列值设置为NULL。
CASCADE : CASCADE will propagate the change when the parent changes. If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.
级联:级联将在父级更改时传播更改。如果删除一行，引用该行的受约束表中的行也将被删除，等等。
RESTRICT : RESTRICT causes you can not delete a given parent row if a child row exists that references the value for that parent row.
限制:如果存在引用父行值的子行，则无法删除给定父行。
NO ACTION : NO ACTION and RESTRICT are very much alike. when an UPDATE or DELETE statement is executed on the referenced table, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. in short child row no concern if parent row delete or update.
没有行动:没有行动和限制是非常相似的。当更新或删除语句在引用的表上执行时，DBMS在语句执行的末尾验证没有任何引用关系被违反。在短子行中，如果父行删除或更新，则不必担心。