Question

I keep getting the same error Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'test_ibfk_5' in the referenced table 'appointment', can you please tell me what is wrong with my code:

-- Table III: Appointment = (site_name [fk7], date, time)
-- fk7: site_name -> Site.site_name
DROP TABLE IF EXISTS appointment;
CREATE TABLE appointment (
   appt_site VARCHAR(100) NOT NULL,
appt_date DATE NOT NULL,
appt_time TIME NOT NULL,
PRIMARY KEY (appt_date, appt_time),
FOREIGN KEY (appt_site) REFERENCES site(site_name)
) ENGINE=innodb;

-- Table IV: Test = (test_id, status, appointment[fk3])
-- fk3: (site_name, date, time) -> Site.site_name, Appointment.date, Appointment.time, appointment is non-null
DROP TABLE IF EXISTS test;
CREATE TABLE test (
   test_id INT NOT NULL,
test_status VARCHAR(100) NOT NULL,
appt_site VARCHAR(100) NOT NULL,
appt_date DATE NOT NULL,
appt_time TIME NOT NULL,
PRIMARY KEY (test_id),
FOREIGN KEY (appt_site) REFERENCES site(site_name),
   CONSTRAINT test_ibfk_4 FOREIGN KEY (appt_date) REFERENCES appointment(appt_date), -- CHECK!!!
   CONSTRAINT test_ibfk_5 FOREIGN KEY (appt_time) REFERENCES appointment(appt_time) -- CHECK!!!
) ENGINE=innodb;

Answer 1

• Definitin of foreign key :

A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

• When creating foreign keys be sure the columns you are using have the same:

1.    Data Type
2. Collation
3. Zero Fill
4. Not Null
5. Unsigned
6. Binary

As shown in above example :

As you are using foreign key as app_site in test and appoinment table but you give reference of site_name attribute which is primary key in sitr table which not satisfy condition for foreign key.

The attributr name must be same for foreign key in other table and primary key in parent table.

the correct code is as below:

DROP TABLE IF EXISTS appointment;
CREATE TABLE appointment (
   appt_site VARCHAR(100) NOT NULL,
appt_date DATE NOT NULL,
appt_time TIME NOT NULL,
PRIMARY KEY (appt_date, appt_time),
FOREIGN KEY (site_name) REFERENCES site(site_name)
) ENGINE=innodb;

DROP TABLE IF EXISTS test;
CREATE TABLE test (
   test_id INT NOT NULL,
test_status VARCHAR(100) NOT NULL,
appt_site VARCHAR(100) NOT NULL,
appt_date DATE NOT NULL,
appt_time TIME NOT NULL,
PRIMARY KEY (test_id),
FOREIGN KEY (site_name) REFERENCES site(site_name),