Mysql

errno: 150 ‘外鍵約束格式不正確’

  • September 29, 2019
CREATE TABLE IF NOT EXISTS `Register_Per` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`DOB` varchar(20) NOT NULL,
`Gender` CHARACTER(8) NOT NULL,
`ConNo` int(11) NOT NULL UNIQUE,
`Email` varchar(50) NOT NULL,
   PRIMARY KEY (`id`)
);


CREATE TABLE IF NOT EXISTS `login`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(20) NOT NULL,
   PRIMARY KEY (`username`),
FOREIGN KEY(`id`) REFERENCES `Register_Per`(`id`) 
);


CREATE TABLE `images` (
`id` int(11) NOT NULL AUTO_INCREMENT UNIQUE,
`file_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`uploaded_on` datetime NOT NULL,
`submittedby` varchar(50),
FOREIGN KEY(`submittedby`) REFERENCES `login`(`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

這要改變什麼..??

如果第二個表使用匹配的 DEFAULT CHARSET 和 COLLATE 選項,它開始在 5.7.27 上工作:

CREATE TABLE IF NOT EXISTS `login`(
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(50) NOT NULL,
   `password` varchar(20) NOT NULL,
   PRIMARY KEY (`username`),
   FOREIGN KEY(`id`) REFERENCES `Register_Per`(`id`) 
) Engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

引用自:https://dba.stackexchange.com/questions/249896