mysql - 由于外键约束，无法在 ubuntu/trusty64 php5-mysql 上创建表

Question

我已将项目从 XAMP 移至 VM->Vagrant->ubuntu/trusty64->Installed(php5-mysql mysql-server mysql-client)

现在，当我尝试创建数据库和表时，出现以下错误。

错误无法创建表properties，外键约束格式不正确。引用的表中没有索引，其中引用的列显示为第一列。

这是我的第一个脚本，所以我假设存在一些错误，但当我使用 XAMP 时一切正常。我一直在阅读有关您使用的引擎和字符集的信息，但我不太了解它或如何在流浪者中编辑它。感谢您的所有帮助代码如下。

MySQL

CREATE TABLE `usertypes`(
`id` INT NOT NULL AUTO_INCREMENT,
`type` CHAR(15),
`permissions` CHAR(15),
PRIMARY KEY (`id`)
);

INSERT INTO `usertypes`(`type`,`permissions`) VALUES ("Administrator", '{"Admin":1}' ), ("Staff", '{"Staff":1}'), ("Basic", '{"Basic":1}'), ("Pro", '{"Pro":1}'), ("Business", '{"Business":1}');

CREATE TABLE `users` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`username` VARCHAR (25) NOT NULL UNIQUE,
`password` VARCHAR (60) NOT NULL,
`usertype` INT NOT NULL DEFAULT 3,
`email` varchar(40) NOT NULL,
`authentication` varchar(32) NOT NULL,
`active` smallint(1) NOT NULL DEFAULT 0,
`newsletter` INT NOT NULL,
`banned` SMALLINT NOT NULL DEFAULT 0,
`user_since` DATE NOT NULL,
`listings` INT DEFAULT 0,
CONSTRAINT fk_usertype FOREIGN KEY (`usertype`) REFERENCES usertypes(`id`),
UNIQUE KEY `users` (`username`,`email`)
);

CREATE TABLE `personal` (
`id` INT PRIMARY KEY,
`first_name` VARCHAR (15) NULL,
`last_name` VARCHAR (15) NULL,
`mobile_phone` CHAR(10) NULL,
`city` CHAR(25) NULL,
`address` VARCHAR (100) NULL,
`postal_code` VARCHAR(6) NULL,
CONSTRAINT fk_personal FOREIGN KEY (`id`)
REFERENCES users(`id`)
);

/* Create Properties table */
CREATE TABLE `propertytypes`(
`id` INT NOT NULL AUTO_INCREMENT,
`type` CHAR(10),
PRIMARY KEY (`id`)
);

INSERT INTO `propertytypes`(`type`) VALUES ("house"), ("duplex"), ("apartment"), ("townhouse"), ("4 plex"),("6 plex"), ("room"), ("commercial"), ("gathering halls");

CREATE TABLE `utilities`(
`id` INT NOT NULL AUTO_INCREMENT,
`options` CHAR(10),
PRIMARY KEY (`id`)
);

INSERT INTO `utilities`(`options`) VALUES ("None"),("Heat"), ("Electricity"), ("Water"), ("TV"), ("Internet");

CREATE TABLE `features`(
`id` INT NOT NULL AUTO_INCREMENT,
`options` CHAR(10),
PRIMARY KEY (`id`)
);

INSERT INTO `features`(`options`) VALUES ("Coin Laundry"), ("Own Laundry"), ("Jetted Tub / Jacuzzi"), ("Gym"), ("Pool"), ("Security"), ("Balcony"), ("Elevator"), ("Hardwood Floors"), ("Fenced Backyard"), ("Dishwasher"), ("Air Conditioning"), ("Laminate Floors"), ("Fire Pit"), ("Fireplace"), ("Wheelchair Access"), ("Storage Lockers");

CREATE TABLE `properties` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `owner` INT NOT NULL,
 `propertytype` INT NOT NULL,
 `address` varchar(255) NOT NULL,
 `postalcode` varchar(8) NOT NULL,
 `beds` INT(10),
 `baths` INT(10),
 `sqf` DECIMAL (6,2),
 `price` DECIMAL (6,2),
 `images` varchar(32),
 `listed` smallint(1) DEFAULT 2,
 `pets` smallint(1) DEFAULT 2,
 `kids` smallint(1) DEFAULT 2,
 `adults` smallint(1) DEFAULT 2,
 `utilities` INT NOT NULL DEFAULT 0,
 `features` INT NOT NULL DEFAULT 0,
 `parking` smallint(1) DEFAULT 2,
 `smokers` smallint(1) DEFAULT 2,
 `deposit` smallint(1) DEFAULT 0,
 `description` TEXT NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT fk_users FOREIGN KEY (`owner`)
REFERENCES users(`id`),
CONSTRAINT fk_propertytypes FOREIGN KEY (`propertytype`)
REFERENCES propertytypes(`id`),
CONSTRAINT fk_utilities FOREIGN KEY (`utilities`)
REFERENCES utilities(`options`),
CONSTRAINT fk_features FOREIGN KEY (`features`)
REFERENCES features(`options`)
);

Answer 1

这一直贯穿

查找显示“CHANGE MADE”的行

未正确指定列。另外，有 2 列在插入过程中被截断。

create schema ff; -- create a new db
use ff; -- now use that db

CREATE TABLE `usertypes`(
`id` INT NOT NULL AUTO_INCREMENT,
`type` CHAR(15),
`permissions` CHAR(15),
PRIMARY KEY (`id`)
);

INSERT INTO `usertypes`(`type`,`permissions`) VALUES ("Administrator", '{"Admin":1}' ), ("Staff", '{"Staff":1}'), ("Basic", '{"Basic":1}'), ("Pro", '{"Pro":1}'), ("Business", '{"Business":1}');

CREATE TABLE `users` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`username` VARCHAR (25) NOT NULL UNIQUE,
`password` VARCHAR (60) NOT NULL,
`usertype` INT NOT NULL DEFAULT 3,
`email` varchar(40) NOT NULL,
`authentication` varchar(32) NOT NULL,
`active` smallint(1) NOT NULL DEFAULT 0,
`newsletter` INT NOT NULL,
`banned` SMALLINT NOT NULL DEFAULT 0,
`user_since` DATE NOT NULL,
`listings` INT DEFAULT 0,
CONSTRAINT fk_usertype FOREIGN KEY (`usertype`) REFERENCES usertypes(`id`),
UNIQUE KEY `users` (`username`,`email`)
);

CREATE TABLE `personal` (
`id` INT PRIMARY KEY,
`first_name` VARCHAR (15) NULL,
`last_name` VARCHAR (15) NULL,
`mobile_phone` CHAR(10) NULL,
`city` CHAR(25) NULL,
`address` VARCHAR (100) NULL,
`postal_code` VARCHAR(6) NULL,
CONSTRAINT fk_personal FOREIGN KEY (`id`)
REFERENCES users(`id`)
);

/* Create Properties table */
-- drop table propertytypes;
CREATE TABLE `propertytypes`(
`id` INT NOT NULL AUTO_INCREMENT,
`type` CHAR(40), -- CHANGE MADE ************************
PRIMARY KEY (`id`)
);

INSERT INTO `propertytypes`(`type`) VALUES ("house"), ("duplex"), ("apartment"), ("townhouse"), ("4 plex"),("6 plex"), ("room"), ("commercial"), ("gathering halls");

-- drop table utilities;
CREATE TABLE `utilities`(
`id` INT NOT NULL AUTO_INCREMENT,
`options` CHAR(40), -- CHANGE MADE ************************
PRIMARY KEY (`id`)
);

INSERT INTO `utilities`(`options`) VALUES ("None"),("Heat"), ("Electricity"), ("Water"), ("TV"), ("Internet");

CREATE TABLE `features`(
`id` INT NOT NULL AUTO_INCREMENT,
`options` CHAR(40), -- CHANGE MADE ************************
PRIMARY KEY (`id`)
);

INSERT INTO `features`(`options`) VALUES ("Coin Laundry"), ("Own Laundry"), ("Jetted Tub / Jacuzzi"), ("Gym"), ("Pool"), ("Security"), ("Balcony"), ("Elevator"), ("Hardwood Floors"), ("Fenced Backyard"), ("Dishwasher"), ("Air Conditioning"), ("Laminate Floors"), ("Fire Pit"), ("Fireplace"), ("Wheelchair Access"), ("Storage Lockers");

CREATE TABLE `properties` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `owner` INT NOT NULL,
 `propertytype` INT NOT NULL,
 `address` varchar(255) NOT NULL,
 `postalcode` varchar(8) NOT NULL,
 `beds` INT(10),
 `baths` INT(10),
 `sqf` DECIMAL (6,2),
 `price` DECIMAL (6,2),
 `images` varchar(32),
 `listed` smallint(1) DEFAULT 2,
 `pets` smallint(1) DEFAULT 2,
 `kids` smallint(1) DEFAULT 2,
 `adults` smallint(1) DEFAULT 2,
 `utilities` INT NOT NULL DEFAULT 0,
 `features` INT NOT NULL DEFAULT 0,
 `parking` smallint(1) DEFAULT 2,
 `smokers` smallint(1) DEFAULT 2,
 `deposit` smallint(1) DEFAULT 0,
 `description` TEXT NOT NULL,
PRIMARY KEY (`id`),

CONSTRAINT fk_users FOREIGN KEY (`owner`)
REFERENCES users(`id`),

CONSTRAINT fk_propertytypes FOREIGN KEY (`propertytype`)
REFERENCES propertytypes(`id`),

CONSTRAINT fk_utilities FOREIGN KEY (`utilities`)
REFERENCES utilities(`id`), -- CHANGE MADE ************************

CONSTRAINT fk_features FOREIGN KEY (`features`)
REFERENCES features(`id`) -- CHANGE MADE ************************
);

drop schema ff; -- drop test db (cleanup)

此外，如果 DEFAULT 0 与从 1 及以上开始的 FK 约束发生冲突，请考虑删除它们。坦率地说，我不明白这有什么意义。

来自标题为 Using FOREIGN KEY Constraints 的 mysql 手册页

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

和

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.