MySql错误代码:1005不能创建表errno: 150。

MySql workbench reports that my syntax is error free. I can't figure out what's wrong with my database. Any ideas?

MySql工作台报告，我的语法是错误的。我不知道我的数据库出了什么问题。什么好主意吗?

CREATE TABLE `users` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `login` VARCHAR(35) NOT NULL UNIQUE,
    `pass` VARCHAR(35) NOT NULL,
    `fname` VARCHAR(35),
    `lname` VARCHAR(35),
    `gender` VARCHAR(1),
    `phone` VARCHAR(12),
    `appointments` INT,
    `groups` INT(1) NOT NULL,
    FOREIGN KEY (`groups`) references `groups`(`gnumber`)
);

CREATE TABLE `groups` (
    `gname` VARCHAR(25) NOT NULL,
    `gnumber` INT(1) NOT NULL
);

INSERT INTO `groups`(`gname`, `gnumber`) values ('user', 0);
INSERT INTO `groups`(`gname`, `gnumber`) values ('admin', 1);
INSERT INTO `users`(`login`, `pass`, `groups`) values ('admin', 'secret', 1);

1 个解决方案

#1

Error Code 150 means:

错误代码150的意思是:

If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.

如果错误消息指向错误150，则表创建失败，因为没有正确地形成外键约束。

To fix this, simply create the groups Table before the users Table.

要解决这个问题，只需在users表之前创建groups表。

Edit: You'll also need to make gnumber a key in the groups table for this to work.

编辑:您还需要将gnumber设置为组表中的键，以使其工作。

CREATE TABLE `groups` (
  `gname` varchar(25) NOT NULL,
  `gnumber` int(1) NOT NULL,
  PRIMARY KEY (`gnumber`)
);

I don't know whether you want it to be the primary key, you'll have to play around with the available settings but this works for me locally insofar that I can create the tables and execute the inserts.

我不知道您是否希望它是主键，您将不得不使用可用的设置，但这对我在本地是适用的，因为我可以创建表并执行插入。

#1