Not the answer you need?
Register and ask your own question!

Foreign key error

ikonraoikonrao ContributorCurrent User Role Advisor
Hello,

Performed INNODB ENGINE STATUS. Got below error in LAST FOREIGN KEY ERROR SECTION:

Please find table sructure provided below. Could anyone please help me to understand this?

ERROR:

MySQL thread id 205914, OS thread handle 0x7f070168c700, query id 1440847 custom copy to tmp table
alter table CTABLE add index FKEF5F18AE1D1C86DC (DRAFT_ID_PK_COL), add constraint FKEF5F18AE1D1C86DC foreign key (DRAFT_ID_PK_COL) references INSURANCE_PROPOSER_DRAFT_TAB (DRAFT_ID_PK_COL)
Foreign key constraint fails for table `DB1`.`#sql-5c5c_3245a`:
,
CONSTRAINT `FKEF5F18AE1D1C86DC` FOREIGN KEY (`DRAFT_ID_PK_COL`) REFERENCES `PTABLE` (`DRAFT_ID_PK_COL`)
Trying to add in child table, in index `PROPOSER_REJ_REM_DRAFT_ID` tuple:
DATA TUPLE: 3 fields;
0: len 8; hex 8000000000000000; asc ;;
1: len 8; hex 8000000000000107; asc ;;
2: len 8; hex 8000000000000002; asc ;;

But in parent table `DB1`.`INSURANCE_PROPOSER_DRAFT_TAB`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 52; compact format; info bits 0
0: len 8; hex 800000000000009b; asc ;;
1: len 6; hex 0000000a24dd; asc $ ;;
2: len 7; hex ff0000028c0110; asc ;;
3: len 5; hex 41646d696e; asc Admin;;
4: len 5; hex 999208bbe9; asc ;;
5: len 5; hex 9992080000; asc ;;
6: len 10; hex 466f7274204d616b6572; asc FM;;
7: len 1; hex 4e; asc N;;
8: len 8; hex 8000000000000000; asc ;;
9: len 1; hex 59; asc Y;;
10: len 0; hex ; asc ;;

Table: CTABLE
Create Table: CREATE TABLE `CTABLE` (
`PROPOSER_ID_PK` bigint(20) NOT NULL,
`PROPOSER_REJECTION_REMARKS_PK` bigint(20) NOT NULL,
`DRAFT_ID_PK_COL` bigint(20) NOT NULL,
PRIMARY KEY (`PROPOSER_ID_PK`,`PROPOSER_REJECTION_REMARKS_PK`),
UNIQUE KEY `PROPOSER_REJECTION_REMARKS_PK` (`PROPOSER_REJECTION_REMARKS_PK`),
KEY `FKEF5F18AEC4EB848F` (`PROPOSER_ID_PK`),
KEY `FKEF5F18AE38BD3439` (`PROPOSER_REJECTION_REMARKS_PK`),
KEY `PROPOSER_REJ_REM_DRAFT_ID` (`DRAFT_ID_PK_COL`),
CONSTRAINT `FKEF5F18AE38BD3439` FOREIGN KEY (`PROPOSER_REJECTION_REMARKS_PK`) REFERENCES `PROPREJECTMARKS` (`PROPOSER_REJECTION_REMARKS_PK`),
CONSTRAINT `FKEF5F18AEC4EB848F` FOREIGN KEY (`PROPOSER_ID_PK`) REFERENCES `INSPRODETAILS` (`PROPOSER_ID_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Table: PTABLE
Create Table: CREATE TABLE `PTABLE` (
`DRAFT_ID_PK_COL` bigint(20) NOT NULL AUTO_INCREMENT,
`CREATED_BY` varchar(255) DEFAULT NULL,
`CREATED_DATE` datetime DEFAULT NULL,
`DATE_OF_RECEIPT` datetime DEFAULT NULL,
`DRAFT_CREATOR` varchar(255) DEFAULT NULL,
`DRAFT_IN_USE` varchar(255) DEFAULT NULL,
`DRAFT_IN_USE_USER` bigint(20) NOT NULL,
`DRAFT_INACTIVE_FLAG` varchar(2) DEFAULT NULL,
`EIA_ACCOUNT_NO` varchar(255) DEFAULT NULL,
`EIA_UPLOAD_FLAG` varchar(2) DEFAULT NULL,
`INSURANCE_CMPNY_CD` varchar(6) DEFAULT NULL,
`MODIFIED_BY` varchar(255) DEFAULT NULL,
`MODIFIED_DATE` datetime DEFAULT NULL,
`NO_OF_PROPOSER` int(11) DEFAULT NULL,
`PANSITENAME` varchar(255) DEFAULT NULL,
`PAN_VERIFICATION_STATUS` varchar(2) DEFAULT NULL,
`POS_MAKER_USERID` bigint(20) DEFAULT NULL,
`POS_ORGANIZATIONID` bigint(20) DEFAULT NULL,
`PROPOSER_ACCOUNT_CATEGORY` varchar(255) DEFAULT NULL,
`PROPOSER_ACCOUNT_STATUS` varchar(255) DEFAULT NULL,
`PROPOSER_CONTINUOUS_KYC` varchar(255) DEFAULT NULL,
`PROPOSER_DATE_OF_BIRTH` datetime DEFAULT NULL,
`PROPOSER_FATHER_HUSBAND_NAME` varchar(255) DEFAULT NULL,
`PROPOSER_FATHER_HUSBAND_NAME_INDICATOR` varchar(255) DEFAULT NULL,
`PROPOSER_FIRST_NAME` varchar(255) DEFAULT NULL,
`PROPOSER_GENDER` varchar(255) DEFAULT NULL,
`ITAX_PROPOSER_NAME` varchar(100) DEFAULT NULL,
`PROPOSER_LAST_NAME` varchar(255) DEFAULT NULL,
`PROPOSER_MARITAL_STATUS` varchar(255) DEFAULT NULL,
`PROPOSER_MIDDLE_NAME` varchar(255) DEFAULT NULL,
`PROPOSER_OCCUPATION` varchar(255) DEFAULT NULL,
`PAN` varchar(255) DEFAULT NULL,
`PROPOSER_RECORD_REFERENCE` varchar(255) DEFAULT NULL,
`PROPOSER_RESIDENTIAL_STATUS` varchar(255) DEFAULT NULL,
`PROPOSER_SCAN_FORM` longblob,
`PROPOSER_SCAN_FORM_NAME` varchar(255) DEFAULT NULL,
`PROPOSER_SCAN_PHOTOGRAPH` longblob,
`PROPOSER_SCAN_PHOTOGRAPH_NAME` varchar(255) DEFAULT NULL,
`PROPOSER_SCAN_SIGNATURE` longblob,
`PROPOSER_SCAN_SIGNATURE_NAME` varchar(255) DEFAULT NULL,
`PROPOSER_TITLE` varchar(255) DEFAULT NULL,
`PROPOSER_TYPE_OF_ACCOUNT` varchar(255) DEFAULT NULL,
`PROPOSER_TYPE_OF_ACCOUNT_OTHERS` varchar(255) DEFAULT NULL,
`UID` varchar(255) DEFAULT NULL,
`RECORD_REFERENCE_ID` varchar(255) DEFAULT NULL,
`RELATIONSHIP` varchar(255) DEFAULT NULL,
`REPRESENTATIVE_ADDRESS_FLAG` varchar(255) DEFAULT NULL,
`SAME_ADDRESS_FLAG` varchar(255) DEFAULT NULL,
`PROPOSER_SINGLE_DOCUMENT` longblob,
`PROPOSER_SINGLE_DOCUMENT_NAME` varchar(255) DEFAULT NULL,
PRIMARY KEY (`DRAFT_ID_PK_COL`),
KEY `PROPOSER_DRAFT_MODIFIED_DATE` (`MODIFIED_DATE`),
KEY `PROPOSER_DRAFT_IN_USE_N_USER` (`DRAFT_IN_USE`,`DRAFT_IN_USE_USER`)
) ENGINE=InnoDB AUTO_INCREMENT=8850 DEFAULT CHARSET=latin1
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.