Cannot add or update a child row: a foreign key constraint fails even if I already added row on parent table

Viewed 81

I'm trying to add a record to the child table (transaction_tb) and I used a value for the foreign key that DOES exist in the parent table's primary key. But why does it still returns me this error mentioned above?

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;

The data I entered are:

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'); 

What went wrong? I tried adding a row to my transaction_tb table for which there are already present matching row coming from the four tables ( custinfo_tb, brokerinfo_tb, stockinfo_tb and transactiontype_tb)

1 Answers

Your custinfo_tb.Col_ID contains 20180111.

You tried to insert transaction_tb.COL_ID value 201808111.

Compare these two values:

20180111
201808111
Related