mysql - 无法添加或更新子行 : a foreign key constraint fails even if I already added row on parent table

Question

我正在尝试向子表 (transaction_tb) 添加一条记录，并且我使用的外键值确实存在于父表的主键中。但是为什么它仍然向我返回上面提到的这个错误？

CREATE TABLE custinfo_tb(
    transactionID bigint NOT NULL,
    Col_ID int(8) NOT NULL,
    email varchar(50) NOT NULL,
    contact int(11) NOT NULL,
    address varchar(100) NOT NULL,
    tdcp varchar(100) NOT NULL,
    pc varchar(4) NOT NULL,
    PRIMARY KEY (Col_ID,transactionID)
    
  
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE brokerinfo_tb
(
 transactionID bigint NOT NULL,
  Brokerid varchar(30) NOT NULL,
  Broker_contact int(15) NOT NULL,
  Account_wBroker varchar(100) NOT NULL,
  PRIMARY KEY (Brokerid,transactionID)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE stockinfo_tb
(
    transactionID bigint NOT NULL,
    Stock_Code varchar(6) NOT NULL,
    Stock_Name varchar(50) NOT NULL,
    Stock_Amount int(9) NOT NULL,
    Avecost float NOT NULL,
    Cert_amount float NOT NULL,
    PRIMARY KEY (stock_code,transactionID)
    
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE transactiontype_tb
(
  transactionID bigint NOT NULL,
  Transfertype varchar(20) NOT NULL,
  transferdesc varchar(20) NOT NULL,
  transperiod varchar(10) NOT NULL,
  transfee varchar(30) NOT NULL,
  PRIMARY KEY (transfertype,transactionID)
    
)ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE transaction_tb(
    transactionID bigint  NOT NULL AUTO_INCREMENT,
    COL_ID int NOT NULL,
    sysdate DATE,
    Stock_Code varchar(6) NOT NULL,
    Brokerid varchar(30) NOT NULL,
    Transfertype varchar(20) NOT NULL,
    PRIMARY KEY (transactionID),
    FOREIGN KEY (Col_ID,transactionID) REFERENCES custinfo_tb(Col_ID,transactionID)  ON DELETE CASCADE ON UPDATE CASCADE ,
    FOREIGN KEY (Brokerid,transactionID) REFERENCES brokerinfo_tb(Brokerid,transactionID)  ON DELETE CASCADE ON UPDATE CASCADE ,
    FOREIGN KEY (Stock_Code,transactionID) REFERENCES stockinfo_tb(Stock_Code,transactionID)  ON DELETE CASCADE ON UPDATE CASCADE ,
    FOREIGN KEY (Transfertype,transactionID) REFERENCES transactiontype_tb(Transfertype,transactionID)  ON DELETE CASCADE ON UPDATE CASCADE
    
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

我输入的数据是:

INSERT INTO brokerinfo_tb (transactionID, Brokerid)
VALUES (1,1);

INSERT INTO custinfo_tb (transactionID, Col_ID)
VALUES (1,20180111);

INSERT INTO stockinfo_tb (transactionID, Stock_Code)
VALUES (1,'AR');

INSERT INTO transactiontype_tb (transactionID, Transfertype)
VALUES (1,'TRANSFER_IN');

-- THis is where it failed
INSERT INTO transaction_tb (transactionID, Brokerid, COL_ID, Stock_Code, Transfertype)
VALUES (1,1,201808111,'AR','TRANSFER_IN'); 

什么地方出了错？我尝试在我的 transaction_tb 表中添加一行，其中已经存在来自四个表(custinfo_tb、brokerinfo_tb、stockinfo_tb 和 transactiontype_tb)的匹配行

Answer 1

您的 custinfo_tb.Col_ID包含 20180111。
您试图插入 transaction_tb.COL_ID值 201808111。
比较这两个值:

20180111
201808111