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