debugsqlModerate
MySQL create table: error 1005 errno: 150 "Foreign key constraint is incorrectly formed"
Viewed 0 times
error1005createforeignconstraintmysqlerrno150incorrectlyformed
Problem
This fails when trying to create the EXCHANGE table:
The last create statement returns:
CREATE TABLE BOOK
(PKACC_NO VARCHAR (20) PRIMARY KEY,
TITLE VARCHAR (50),
AUTHOR VARCHAR (50),
PUBLISHER VARCHAR (50),
EDITION INTEGER (4),
PRICE INTEGER (10));
CREATE TABLE MEMBER
(PKMEMBER_ID VARCHAR (20) PRIMARY KEY,
NAME VARCHAR (50),
TYPE VARCHAR (10),
CONTACT_NO BIGINT (10),
ADHAAR_NO BIGINT(12),
LAST_VISIT DATE );
CREATE TABLE STAFF
(PKSTAFF_ID VARCHAR (20),
NAME VARCHAR(50),
ADHAAR_NO BIGINT (12),
CONTACT_NO BIGINT(10) );
CREATE TABLE EXCHANGE
(TRANSACTION_ID VARCHAR(20) PRIMARY KEY,
FK1ACC_NO VARCHAR(20),
FK2MEMBER_ID VARCHAR (20),
FK3STAFF_ID VARCHAR(20),
DATE_OF_ISSUE DATE,
DATE_OF_RETURN DATE,
FINE INTEGER(20),
REMARKS VARCHAR(20),
CONSTRAINT FK1 FOREIGN KEY (FK1ACC_NO) REFERENCES BOOK (PKACC_NO)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT FK2 FOREIGN KEY (FK2MEMBER_ID) REFERENCES MEMBER (PKMEMBER_ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT FK3 FOREIGN KEY (FK3STAFF_ID) REFERENCES STAFF (PKSTAFF_ID)
ON DELETE CASCADE
ON UPDATE CASCADE);The last create statement returns:
ERROR 1005 (HY000): Can't create table `testdb`.`EXCHANGE` (errno: 150 "Foreign key constraint is incorrectly formed")Solution
At times the best way to overcome an error within your InnoDB table is to check the InnoDB table status by using this command.
SHOW ENGINE INNODB STATUS;
This will enlighten you on what you are doing wrong.
SHOW ENGINE INNODB STATUS;
This will enlighten you on what you are doing wrong.
Context
StackExchange Database Administrators Q#190153, answer score: 14
Revisions (0)
No revisions yet.