Cannot add foreign keys constraint

Question

CREATE TABLE tblTransaction (
   strTransCode VARCHAR(50) NOT NULL,
   dtmTransDate datetime,
   strTransDesc VARCHAR(50) NOT NULL,
   dblTransAmt double,
   intVoucRefCodeTrans INT,
   FOREIGN KEY (intVoucRefCodeTrans) REFERENCES tblVoucher (intVoucRefCode) ON DELETE RESTRICT ON UPDATE CASCADE,
   PRIMARY KEY (strTransCode)
   )ENGINE=InnoDB;
CREATE TABLE tblVoucher (
   intVoucRefCode INT,
   strVoucRefDesc VARCHAR(50) NOT NULL,
   dtmVoucDate datetime,
   PRIMARY KEY (intVoucRefCode)
   )ENGINE=InnoDB;

these are my tables I don't know why it displays "Cannot add foreign key constraint" please help

change the order, one table needs to exist before the other.. — amdixon
– amdixon, Commented Aug 10, 2015 at 12:35

M Khalid Junaid · Accepted Answer · 2015-08-10 12:36:05Z

1

First create tblVoucher table then reference it in tblTransaction table

CREATE TABLE tblVoucher (
   intVoucRefCode INT,
   strVoucRefDesc VARCHAR(50) NOT NULL,
   dtmVoucDate datetime,
   PRIMARY KEY (intVoucRefCode)
   )ENGINE=InnoDB;

CREATE TABLE tblTransaction (
   strTransCode VARCHAR(50) NOT NULL,
   dtmTransDate datetime,
   strTransDesc VARCHAR(50) NOT NULL,
   dblTransAmt double,
   intVoucRefCodeTrans INT,
   FOREIGN KEY (intVoucRefCodeTrans) REFERENCES tblVoucher (intVoucRefCode) ON DELETE RESTRICT ON UPDATE CASCADE,
   PRIMARY KEY (strTransCode)
   )ENGINE=InnoDB;

DEMO

answered Aug 10, 2015 at 12:36

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Drew Over a year ago

be careful with wide PK's like you have in tblTransaction table

Collectives™ on Stack Overflow

Cannot add foreign keys constraint

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related