InnoDB mySQL无法设置"ON DELETE SET DEFAULT".怎么设置? [英] InnoDB mySQL unable to set "ON DELETE SET DEFAULT'. How to set?

问题描述

我正在尝试创建一个名为EMPLOYEE的表.当我使用以下语句而不使用"ON DELETE SET DEFAULT"时，它正在工作.

I am trying to create a table named EMPLOYEE. When I use the following statements without "ON DELETE SET DEFAULT" it is working.

这是我在"ON DELETE SET DEFAULT"时遇到的错误:

Here is the Error I get with "ON DELETE SET DEFAULT":

ERROR 1005 (HY000): Can't create table 'COMPANY.EMPLOYEE' (errno: 150)

这里是DDL

CREATE TABLE EMPLOYEE ( 
    Fname VARCHAR(15) NOT NULL, 
    Minit CHAR, Lname VARCHAR(15) NOT NULL, 
    Ssn CHAR(9) NOT NULL DEFAULT '123456789',
    Bdate DATE, ADDRESS VARCHAR(30), 
    Sex CHAR, Salary DECIMAL(10,2), 
    Super_Ssn CHAR(9) NOT NULL DEFAULT '123456789', 
    Dno INT NOT NULL DEFAULT -99,
    PRIMARY KEY (Ssn), 
    FOREIGN KEY (Super_Ssn) REFERENCES COMPANY.EMPLOYEE(Ssn) 
        ON DELETE SET DEFAULT 
        ON UPDATE CASCADE )ENGINE=InnoDB; 

请帮助我！！！并预先感谢:)

Please help me!!! and Thanks in advance :)

推荐答案

您不能在InnoDB中使用ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT

You can't use ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT with InnoDB

InnoDB和FOREIGN KEY约束
尽管MySQL服务器允许使用SET DEFAULT，但拒绝它的原因是 InnoDB无效.使用此的CREATE TABLE和ALTER TABLE语句 子句不适用于InnoDB表.

InnoDB and FOREIGN KEY Constraints
While SET DEFAULT is allowed by the MySQL Server, it is rejected as invalid by InnoDB. CREATE TABLE and ALTER TABLE statements using this clause are not allowed for InnoDB tables.

如果适合您，可以尝试ON DELETE SET NULL

You may try ON DELETE SET NULL if it fits your needs

如果ON UPDATE CASCADE或ON UPDATE SET NULL递归以更新相同 它之前在级联期间已更新的表，其行为类似于 严格.这意味着您不能使用自引用的ON UPDATE CASCADE或ON UPDATE SET NULL操作.这是为了防止无限 级联更新导致的循环. 自我参照的删除" 另一方面，可以使用SET NULL，也可以使用自引用ON. 删除级联.级联操作不能嵌套超过15个 深层次

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于InnoDB mySQL无法设置"ON DELETE SET DEFAULT".怎么设置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！