我有两个表,'po'和'receive'
CREATE TABLE `po` (
`PO_ID` bigint(20) NOT NULL,
`SERVICE_TYPE` bit(1) DEFAULT NULL,
`ENTRY_DATE` date NOT NULL,
`RECEIPT_DATE` date DEFAULT NULL,
`TURNOVER` date DEFAULT NULL,
`MOBILIZATION` date DEFAULT NULL,
`SITE_NAME` varchar(255) NOT NULL,
`SITE_CODE` varchar(45) DEFAULT NULL,
`SITE_TIN` varchar(45) DEFAULT NULL,
`SITE_ADDRESS` varchar(255) NOT NULL,
`COST` decimal(11,2) NOT NULL,
`XML` text,
PRIMARY KEY (`PO_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
CREATE TABLE `receive` (
`RECEIPT_ID` varchar(100) NOT NULL,
`RECEIPT_DATE` date NOT NULL,
`PO_NUMBER` bigint(20) NOT NULL,
`SUPPLIER_ID` int(11) NOT NULL,
PRIMARY KEY (`RECEIPT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
我试图通过在表"receive"上定义外键"fk_po"来连接两个表
ALTER TABLE `fourthwave`.`receive`
ADD CONSTRAINT `fk_po`
FOREIGN KEY (`PO_NUMBER` )
REFERENCES `fourthwave`.`po` (`PO_ID` )
ON DELETE SET NULL
ON UPDATE SET NULL
, ADD INDEX `fk_po` (`PO_NUMBER` ASC)
然而,上面的alter查询抛出了一个错误:
Error Code: 1005. Can't create table 'fourthwave.#sql-aec_11' (errno:150)
我收到这个错误是因为两个表上的字段名称"PO_ID"one_answers"PO_NUMBER"不同吗?
在ALTER TABLE之后执行SHOW ENGINE INNODB STATUS语句,您将看到错误消息-"您已经定义了一个SET NULL条件,尽管列定义为NOT NULL。
ALTER TABLE `receive`
ADD CONSTRAINT `fk_po`
FOREIGN KEY (`PO_NUMBER` )
REFERENCES `po` (`PO_ID` )
ON DELETE SET NULL
ON UPDATE SET NULL
, ADD INDEX `fk_po` (`PO_NUMBER` ASC);
SHOW ENGINE INNODB STATUS;
您需要receive
表中PO_NUMBER
的索引。您在外键中引用的字段应始终编入索引。