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

This is the SQL:

这是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;

I got this error message from the above code:

我从上面的代码中收到此错误消息:

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

---

However, if I change

但是,如果我改变了

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

to

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

the code works.

代码有效。

1 个解决方案

#1

I reckon the problem here is that you are specifying columns in your issue table primary key definition to be set to null in the event of their parent row being deleted. MySQL will not like this since primary key columns are not allowed to contain null values.

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

A quick tweak to the DDL of the issue table should allow you to do what you want. One of the key (no pun intended) differences between a primary key and a unique key is that unique key columns are allowed to contain null values. I'm taking a guess that the issueID column will be unique too given that it is specified as AUTO_INCREMENT. Try the following:

对问题表的DDL进行快速调整可以让您按照自己的意愿行事。主键和唯一键之间的关键(没有双关语)差异之一是允许唯一键列包含空值。我猜测issID列也是唯一的,因为它被指定为AUTO_INCREMENT。请尝试以下方法:

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;

Good luck!