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

This is the 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(10开发者_如何学C0) 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.

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.

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:

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!