MYSQL外键问题

Question

我正在尝试设置外键，但遇到了问题(并收到非常有用的 1064“你做错了”错误)

要创建的代码:

CREATE TABLE `airport`(
`id` int primary key AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`rwyCount` int,
`lat` float(4),
`lon` float(4),
`type` int, 
FOREIGN KEY (type) REFERENCES apType(id),
)ENGINE=MyISAM DEFAULT CHARSET=latin1;

我可以让它不给我这个错误的唯一方法是删除外键行。我在这里做错了什么？

这是 apType 表的代码，非常简单:

CREATE TABLE `apType`(
`id` int AUTO_INCREMENT,
`type` varchar(255) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

要强制执行外键约束，您需要使用 Innodb 而不是 MyISAM。 MyISAM parses and then ignores foreign key constraints 。

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.

您还需要declare either a primary key constraint或引用列的唯一约束。 (此处的“id”列。)

...the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.

CREATE TABLE `apType`(
  `id` int AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET=latin1;

在 MySQL 上，最好不要尝试内联声明主键约束。

CREATE TABLE `airport`(
  `id` int AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `rwyCount` int,
  `lat` float(4),
  `lon` float(4),
  `type` int, 
  PRIMARY KEY (id),
  FOREIGN KEY (type) REFERENCES apType(id)
)ENGINE=INNODB DEFAULT CHARSET=latin1;