I have multiple tables and they all seem to be fine but there is this one table which I'm trying to create but it wont work because I am keep on getting Error1005 "Foreign key constraint is incorrectly formed"
.
These are the two tables. I don't know what seems to be the problem.
CREATE TABLE Patient(
ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
Name VARCHAR(255) NOT NULL,
Age TINYINT UNSIGNED,
Sex VARCHAR(10),
Contact INT(11),
Email TEXT(2083),
PRIMARY KEY(ID)
);
CREATE TABLE Appointments (
Appointment_No INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
Name VARCHAR(255) NOT NULL,
Contact INT(11),
Date DATE NOT NULL,
Time TIME NOT NULL,
Reason TEXT(2083),
PRIMARY KEY(Appointment_No),
FOREIGN KEY (Name, Contact) REFERENCES Patient (Name, Contact)
);
As per me, table structure should be like below: