SQLException：无法创建表 errno 150/外键

发布于 2025-01-07 19:05:33 字数 784 浏览 3 评论 0原文

经过一些研究后，当表引擎类型不匹配或外键创建由于某种原因失败时，似乎会出现 errno 150。无论如何，我的创建脚本看起来像这样：

CREATE TABLE IF NOT EXISTS Users 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    fooType INT UNSIGNED NOT NULL DEFAULT 0,
    ...More columns, nothing special...
    PRIMARY KEY (`id`), 
    CONSTRAINT Users_fooType_fk 
        FOREIGN KEY (fooType) 
        REFERENCES FooTypes (id)
        ON DELETE CASCADE
) ENGINE = InnoDB#

CREATE TABLE IF NOT EXISTS FooTypes
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ...More columns, nothing special...
    PRIMARY KEY (`id`) 
) ENGINE = InnoDB#

因此，我的表引擎显式和隐式匹配（我使用的是 MySQL 5.1.49，据我所知，它默认使用 InnoDB）。

所以唯一的区别是 Users.fooType 有一个默认值。这是问题所在吗？如果是的话，我该如何设置默认值？如果不是，有人发现代码还有其他问题吗？抱歉，我不得不稍微混淆它，但是您必须相信我的话，代码的混淆块是正确的/不相关的。

原文

After doing some research, it would seem that errno 150 occurs when either table engine types don't match, or when foreign key creation fails for whatever reason. Anyway, my Create script looks like this:

CREATE TABLE IF NOT EXISTS Users 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    fooType INT UNSIGNED NOT NULL DEFAULT 0,
    ...More columns, nothing special...
    PRIMARY KEY (`id`), 
    CONSTRAINT Users_fooType_fk 
        FOREIGN KEY (fooType) 
        REFERENCES FooTypes (id)
        ON DELETE CASCADE
) ENGINE = InnoDB#

CREATE TABLE IF NOT EXISTS FooTypes
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ...More columns, nothing special...
    PRIMARY KEY (`id`) 
) ENGINE = InnoDB#

So, my table engines match both explicitly and implicitly (I'm using MySQL 5.1.49, which uses InnoDB by default AFAIK).

So the only difference is that Users.fooType has a default. Is that the problem? If it is, how can I set up a default? If it isn't, anybody see anything else wrong with the code? Sorry I had to obfuscate it slightly, but you'll have to take my word for it that the obfuscated chunks of the code are correct/not related.

分享到QQ

分享到微博