错误 1005 (HY000): 无法创建表......\issue.frm' （错误号：150）

Question

这是 SQL：

CREATE TABLE user (
    userID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    username VARCHAR(100) NOT NULL,
    isAdmin BOOL NOT NULL DEFAULT 0,
    canAssignIssue BOOL NOT NULL DEFAULT 0,
    canMarkDuplicate BOOL NOT NULL DEFAULT 0,
    canProcessIssue BOOL NOT NULL DEFAULT 0
) ENGINE = InnoDB;

CREATE TABLE issue (
    issueID INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,
    title VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    duplicateOf INTEGER UNSIGNED DEFAULT NULL,
    issueDateTime DATETIME NOT NULL,
    postBy INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (issueID, postBy, duplicateOf),
    INDEX (postBy, duplicateOf), 
    FOREIGN KEY (duplicateOf) REFERENCES issue (issueID)
        ON DELETE SET NULL,
    FOREIGN KEY (postBy) REFERENCES user (userID)
        ON DELETE SET NULL
) ENGINE = InnoDB;

我从上面的代码中得到了这个错误消息：

ERROR 1005 (HY000): Can't create table '......\issue.frm' (errno: 150)

---

但是，如果我更改

    FOREIGN KEY (duplicateOf) REFERENCES issue (issueID)
        ON DELETE SET NULL,

为

    FOREIGN KEY (duplicateOf) REFERENCES issue (issueID)
        ON DELETE NO ACTION,

代码就可以了。

Answer 1

我认为这里的问题是您在问题表主键定义中指定列，以便在其父行被删除时将其设置为空。 MySQL 不会喜欢这样，因为主键列不允许包含空值。

对问题表的 DDL 进行快速调整应该可以让您做您想做的事情。主键和唯一键之间的关键（无双关语）区别之一是唯一键列允许包含空值。我猜测 issuesID 列也将是唯一的，因为它被指定为 AUTO_INCRMENT。尝试以下操作：

CREATE TABLE issue (
issueID INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,
title VARCHAR(100) NOT NULL,
body TEXT NOT NULL,    
duplicateOf INTEGER UNSIGNED,
issueDateTime DATETIME NOT NULL,
postBy INTEGER UNSIGNED NULL,
PRIMARY KEY (issueID),
UNIQUE INDEX (issueID,duplicateOf,postBy),
INDEX (postBy, duplicateOf), 
FOREIGN KEY (duplicateOf) REFERENCES issue (issueID)
ON DELETE SET NULL,
FOREIGN KEY (postBy) REFERENCES user (userID)
ON DELETE SET NULL)
ENGINE = InnoDB;

祝你好运！