错误代码1005，SQL状态HY000：无法创建表错误号：150

I'm trying to create a table but the script fails as soon as my netbeans errors the first table of DB.

我正在尝试创建一个表,但是一旦我的netbeans错误第一个DB表,脚本就会失败。

How can this be solved?

怎么解决这个问题?

CREATE TABLE filmy
(
    Film_Id int NOT NULL,
    Nazwa varchar(250),
    Adres varchar(250),
    Data_Utworzenia date,
    Komentarz varchar(250),
    Gat_Id int,
    Sub_Id int,
    Aut_Id int,
    User_Id int,

    Primary Key (Film_Id),
    CONSTRAINT fk_GatFilmy FOREIGN KEY (Gat_Id) REFERENCES gatunek(Gat_Id),
    CONSTRAINT fk_SubFilmy FOREIGN KEY (Sub_Id) REFERENCES subgatunek(Sub_Id),
    CONSTRAINT fk_AutFilmy FOREIGN KEY (Aut_Id) REFERENCES autor(Aut_Id),
    CONSTRAINT fk_UserFilmy FOREIGN KEY (User_Id) REFERENCES users(User_Id)
)

5 个解决方案

#1

Use show innodb status - buried in the output (around the middle) is a "last foreign key error" section. It'll explain exactly why the table creation failed.

使用show innodb status - 埋在输出中(中间)是“最后一个外键错误”部分。它将解释为什么表创建失败。

usually it's due to a reference FK field not existing (typo, wrong table), or there's a field-type mismatch. FK-linked fields must match definitions exactly. A char(1) field can't be FK'd to a char(5) field, etc...

通常它是由于参考FK字段不存在(错字,错误的表),或者存在字段类型不匹配。 FK链接的字段必须与定义完全匹配。 char(1)字段不能FK到char(5)字段等...

Note: In MySQL 5.5, the command for this is show engine innodb status (thanks kewpiedoll99)

注意:在MySQL 5.5中,此命令是show engine innodb status(thanks kewpiedoll99)

#2

Here is my solution:

这是我的解决方案:

CREATE TABLE filmy 
( 
    Film_Id           int           NOT NULL, 
    Nazwa             varchar(250)      NULL, 
    Adres             varchar(250)      NULL, 
    Data_Utworzenia   date              DEFAULT '0000-00-00', 
    Komentarz         varchar(250)      NULL, 
    Gat_Id            int               NULL, 
    Sub_Id            int               NULL, 
    Aut_Id            int               NULL, 
    User_Id           int               NULL, 
 Primary Key (Film_Id, Gat_Id, Sub_Id, Aut_Id, User_Id )
) ENGINE=INNODB;

Foreign key constraints are done after creating the gat, sub, aut & user or else the ide does not know have the two tables existing to make the table constraint a reality! Try: alter table filmy add constraint gatfilmy foreign key (gat_id) references gat(gat_id) on update restrict on delete restrict you have to keep consistent; either name the table gat or gatunek, it cannot be both. How will the cpu know which is gat or gatunek if you do not define them both? Now try with the rest constraints and remember you ahve to create all the tables before you can alter them!

外键约束是在创建gat,sub,aut和user之后完成的,否则ide不知道是否存在两个表以使表约束成为现实!尝试:alter table filmy add constraint gatfilmy foreign key(gat_id)引用gat(gat_id)on update restrict on delete限制你必须保持一致;无论是表格gat还是gatunek,它都不能同时存在。如果你没有定义它们,cpu将如何知道哪个是gat或gatunek?现在尝试使用其余约束并记住你要创建所有表,然后才能改变它们!

CONSTRAINT fk_GatFilmy FOREIGN KEY (Gat_Id) REFERENCES gatunek(Gat_Id), 
CONSTRAINT fk_SubFilmy FOREIGN KEY (Sub_Id) REFERENCES subgatunek(Sub_Id), 
CONSTRAINT fk_AutFilmy FOREIGN KEY (Aut_Id) REFERENCES autor(Aut_Id), 
CONSTRAINT fk_UserFilmy FOREIGN KEY (User_Id) REFERENCES users(User_Id)

#3

The code 150 is a foreign key error.

代码150是外键错误。

One of the referenced tables or columns does not exist (yet, maybe later in your script) or doesn't match type/length/collation/charset. Comment them out in turn to see which one.

其中一个引用的表或列不存在(但可能在您的脚本中稍后)或与类型/长度/整理/字符集不匹配。依次评论它们以查看哪一个。

Or run separate ALTER TABLE commands after all your CREATEs have run

或者在所有CREATE运行后运行单独的ALTER TABLE命令

#4

I usually get that error when I try to add a foreign key for a column that doesn't have an index on it; I notice that none of your associated columns do in the SQL shown.

当我尝试为没有索引的列添加外键时,我通常会收到该错误;我注意到所显示的SQL中没有任何关联列。

#5

May you used with this table name(filmy) in a relation to other table and then you dropped it. check any relation and remove every where you use with this table name or change your table name for example use "filmy1" I changed my table name then it worked.

你可以使用这个表名(filmy)与其他表的关系,然后你删除它。检查任何关系并删除您使用此表名称的每个位置或更改您的表名称例如使用“filmy1”我更改了我的表名称然后它工作。

I hope this work.

我希望这项工作。

#1