不要犹豫在数据库上设置约束条件。这样可以确保数据库的一致性,这也是使用数据库的好理由之一。特别是如果有多个应用程序请求它(或者只有一个应用程序但使用不同来源的直接模式和批处理模式),更应该如此。
与 postgreSQL 不同,在 MySQL 中没有像您在 postgreSQL 中所拥有的高级约束条件,但至少外键约束条件相当先进。
我们将以一个示例为例,其中包含来自这些公司的人员的用户表和公司表。
CREATE TABLE COMPANY (
company_id INT NOT NULL,
company_name VARCHAR(50),
PRIMARY KEY (company_id)
) ENGINE=INNODB;
CREATE TABLE USER (
user_id INT,
user_name VARCHAR(50),
company_id INT,
INDEX company_id_idx (company_id),
FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;
让我们看一下ON UPDATE从句:
- ON UPDATE RESTRICT:默认值:如果您尝试在表COMPANY中更新company_id,则引擎将拒绝该操作,如果至少有一个USER链接到此公司。
- ON UPDATE NO ACTION:与RESTRICT相同。
- ON UPDATE CASCADE:通常是最好的:如果您在表COMPANY的一行中更新company_id,则引擎将相应地在所有引用此公司的USER行上更新它(但不会在USER表上激活触发器,警告)。引擎将为您跟踪更改,这很好。
- ON UPDATE SET NULL:如果您在表COMPANY的一行中更新company_id,则引擎将相关的USER company_id设置为NULL(应在USER company_id字段中可用)。我无法看出在更新时可以做任何有趣的事情,但我可能错了。
现在来看看ON DELETE方面:
- ON DELETE RESTRICT:默认选项:如果您尝试删除表COMPANY中的company_id Id,但至少有一个USER链接到此公司,则引擎将拒绝该操作,可以保护您的数据。
- ON DELETE NO ACTION:与RESTRICT相同。
- ON DELETE CASCADE:危险选项:如果您删除表COMPANY中的一行,则引擎也将删除相关的USERs。这很危险,但可用于在次要表上进行自动清理(因此可能是您想要的内容,但几乎肯定不适用于COMPANY<->USER示例)。
- ON DELETE SET NULL:方便选项:如果您删除COMPANY行,则相关的USERs将自动关系为NULL。如果Null是用户没有公司的值,那么这可能是一个好的行为,例如,您可能需要在应用程序中保留用户作为某些内容的作者,但删除公司对您来说不是问题。
通常我的默认值是:ON DELETE RESTRICT ON UPDATE CASCADE。对于跟踪表(日志-不是所有日志-等等)和主表是包含外键的表的“简单属性”时,使用一些ON DELETE CASCADE
和ON DELETE SET NULL
,例如USER表的JOB表。
编辑
我写这篇文章已经有很长时间了。现在我认为我应该添加一个重要的警告。MySQL在级联方面有一个大的已记录限制。 级联不会触发触发器。因此,如果你足够自信使用触发器来使用该引擎,那么你应该避免级联约束。
MySQL触发器仅对通过SQL语句更改表的操作激活。它们不会对视图的更改或通过不向MySQL服务器传输SQL语句的API更改表的操作进行激活。
==>请看下面的最新编辑,该域名上的事情正在发生变化
触发器不会被外键操作激活。
我认为这个问题不会在未来得到解决。外键约束由InnoDb存储管理,而触发器由MySQL SQL引擎管理。两者是分开的。Innodb是唯一具有约束管理的存储,也许他们将来会直接在存储引擎中添加触发器,也许不会。
但是我有自己的意见,关于你应该选择哪个元素,究竟是贫弱的触发器实现还是非常有用的外键约束支持。一旦你习惯了数据库的一致性,你就会喜欢PostgreSQL。
12/2017-更新有关MySQL的此次编辑:
如@IstiaqueAhmed在评论中所述,这个问题的情况已经发生了变化。因此,请跟随链接并检查最新的情况(可能在未来再次更改)。
ON DELETE CASCADE : dangerous
-- 这句话需要留有余地。它的意思是在删除相关联的数据时会引起连锁反应,可能会造成一些危险。 - onedaywhen