mysql - Can't create table `cms`.`invoice` (errno: 150 "Foreign key constraint is incorrectly formed")

Question

What did I do wrong in the INVOICE table?

This is the report :

2021-01-25 13:49:23 0x6820 Error in foreign key constraint of table hmsarea.invoice:

FOREIGN KEY (VISIT_ID) REFERENCES VISIT(VISIT_ID), CONSTRAINT FK_INVOICEAPT FOREIGN KEY (APPOINTMENT_ID) REFERENCES APPOINTMENT(APPOINTMENT_ID), CONSTRAINT FK_INVOICETRT FOREIGN KEY (TREATMENT_ID) REFERENCES TREATMENT(TREATMENT_ID) ):

CREATE TABLE VISIT (
VISIT_ID INT,
PATIENT_ID INT,
VISIT_DATE VARCHAR(50),
CONSTRAINT FK_V1 FOREIGN KEY (DOCTOR_ID)
  REFERENCES DOCTOR(DOCTOR_ID),
CONSTRAINT FK_V2 FOREIGN KEY (PATIENT_ID)
  REFERENCES PATIENT(PATIENT_ID)
);

CREATE TABLE APPOINTMENT (
APPOINTMENT_ID INT,
APPOINTMENT_DATE VARCHAR(50),
PATIENT_ID INT,
PRIMARY KEY (APPOINTMENT_ID),
CONSTRAINT FK_APP1 FOREIGN KEY (DOCTOR_ID)
  REFERENCES DOCTOR(DOCTOR_ID),
CONSTRAINT FK_APP2 FOREIGN KEY (PATIENT_ID)
  REFERENCES PATIENT(PATIENT_ID)
);

CREATE TABLE TREATMENT (
TREATMENT_ID INT,
TREATMENT_TYPE VARCHAR(100),
PATIENT_ID INT,
TREATMENT_DATE VARCHAR(50),
PRIMARY KEY (TREATMENT_ID),
CONSTRAINT FK_TR1 FOREIGN KEY (DOCTOR_ID)
  REFERENCES DOCTOR(DOCTOR_ID),
CONSTRAINT FK_TR2 FOREIGN KEY (PATIENT_ID)
  REFERENCES PATIENT(PATIENT_ID)
);

CREATE TABLE INVOICE (
INVOICE_NO INT,
INVOICE_TOTAL DECIMAL(5,2) NOT NULL,
INVOICE_DATE VARCHAR(25),
PRESCRIPTION_ID INT,
VISIT_ID INT,
APPOINTMENT_ID INT NULL,
TREATMENT_ID INT NULL,
PATIENT_INT INT,
PATIENT_FIRSTNAME VARCHAR(25),
PRIMARY KEY (INVOICE_NO),
CONSTRAINT FK_INVOICE1 FOREIGN KEY (VISIT_ID)
  REFERENCES VISIT(VISIT_ID),
CONSTRAINT FK_INVOICE2 FOREIGN KEY (APPOINTMENT_ID)
REFERENCES APPOINTMENT(APPOINTMENT_ID),
CONSTRAINT FK_INVOICE3 FOREIGN KEY (TREATMENT_ID)
REFERENCES TREATMENT(TREATMENT_ID)
);

question from:https://stackoverflow.com/questions/65879615/cant-create-table-cms-invoice-errno-150-foreign-key-constraint-is-incorr

Answer 1

fiddle

Failed to add the foreign key constraint. Missing index for constraint 'FK_INVOICEVISIT' in the referenced table 'VISIT'

VISIT.VISIT_ID is not indexed.

Key column 'PATIENT_ID' doesn't exist in table

The column name is PATIENT_INT.

All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

RECEIPT_NO is defined as NULL explicitly.