为什么我使用以下sql命令获得“无法添加外键约束”错误？

I am getting an error while running the following commands on a MySQL server.

我在MySQL服务器上运行以下命令时出错。

CREATE TABLE users (
    userID INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(userID),
    firstname VARCHAR(100) NOT NULL,
    lastname VARCHAR(100) NOT NULL,
    username VARCHAR(120) NOT NULL UNIQUE,
    hashpass VARCHAR(100) NOT NULL
) ENGINE=INNODB ;


CREATE TABLE posts (
    postID INT AUTO_INCREMENT,
    text VARCHAR(1000),
    imageURI VARCHAR(100),
    userID VARCHAR(120) NOT NULL, 

    PRIMARY KEY(postID),

    FOREIGN KEY (userID)
    REFERENCES users (userID)
    ON DELETE CASCADE,

    tags VARCHAR(500)
) ENGINE=INNODB;

I am getting the following error:

我收到以下错误:

Cannot add foreign key constraint

2 个解决方案

#1

Try to use the same type of your primary keys - users.userID int - on the foreign keys - posts.userID - as well.

尝试在外键 - posts.userID - 上使用相同类型的主键 - users.userID int。

WORKING SQL FIDDLE

工作SQL FIDDLE

Try like this:

试试这样:

CREATE TABLE users (
userID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(userID),
firstname VARCHAR(100) NOT NULL,
lastname VARCHAR(100) NOT NULL,
username VARCHAR(120) NOT NULL UNIQUE,
hashpass VARCHAR(100) NOT NULL
) ENGINE=INNODB ;


CREATE TABLE posts (

postID INT AUTO_INCREMENT,
text VARCHAR(1000),
imageURI VARCHAR(100),
userID INT NOT NULL,     <---Change here

PRIMARY KEY(postID),

FOREIGN KEY (userID)
REFERENCES users (userID)
ON DELETE CASCADE,

tags VARCHAR(500)
) ENGINE=INNODB;

#2

your foreign key

你的外键

userID VARCHAR(120) NOT NULL,

is different with

与...不同

userID INT NOT NULL AUTO_INCREMENT,

#1