So I'm trying to create a database in mySQL. I have a bunch of create table statements and a bunch of alter table statements. However, about half of the alter table statements are randomly giving me the 1215 "cannot add foreign key constraint" error, which is about as useful as a doctor telling me I have a broken bone and not telling me which one. Anyways, here is one of the ones that fails:
ALTER TABLE trip_instance ADD FOREIGN KEY (Trip_ID) REFERENCES signups(Trip_ID);
And here are the relevant create statements for that specific one. I made sure to specify that the columns are primary keys.
CREATE TABLE Trip_Instance (Instance_ID INT, Trip_Date DATE, Trip_ID INT, Employee_Leader VARCHAR(50), Employee_assistant VARCHAR(50),PRIMARY KEY(Instance_ID, Trip_ID));
CREATE TABLE Signups (Customer_ID INT, Trip_ID INT, Insurance_Form BOOLEAN, PRIMARY KEY (Customer_ID, Insurance_Form, Trip_ID));
EDIT: So I used:
set foreign_key_checks=0;
Without really knowing what it does, and instead of error code 1215, I now get a bunch of 1822/1825 error codes about indexes, but even when I add index statements to the create table statements, it fails telling me the same issue.
I'm just going to put all my statements below, maybe there is some big thing I'm missing:
CREATE DATABASE OAG_Club;
USE OAG_Club;
CREATE TABLE Customer (customer_ID INT, Name VARCHAR(50), Home_Phone VARCHAR(10), Work_Phone VARCHAR(10), DOB DATE, Address VARCHAR(50), Customer_Type VARCHAR(50), Sponsor_ID INT, PRIMARY KEY(customer_ID));
CREATE TABLE Signups (Customer_ID INT, Trip_ID INT, Insurance_Form BOOLEAN, PRIMARY KEY (Customer_ID, Insurance_Form, Trip_ID));
CREATE TABLE Trip_Instance (Instance_ID INT, Trip_Date DATE, Trip_ID INT, Employee_Leader VARCHAR(50), Employee_assistant VARCHAR(50),PRIMARY KEY(Instance_ID, Trip_ID));
CREATE TABLE Trip_Type (Trip_ID INT, Trip_Name VARCHAR(50), DIFF_lvl INT, Trip_Fee INT, Trip_Length INT, PRIMARY KEY (Trip_ID));
CREATE TABLE Rental_Agreement (Agreement_Number INT, Start_Date DATE, customer_ID INT, employee_ID INT, PRIMARY KEY (Agreement_Number));
CREATE TABLE Rental_Detail (Expected_Return DATE, Real_Return DATE, item_number INT, agreement_num INT);
CREATE TABLE Inventory (Item_Number INT, `Condition` VARCHAR(50), equip_name VARCHAR(50), PRIMARY KEY (Item_Number));
CREATE TABLE Equipment (Equip_Name VARCHAR(50), Student_Fee FLOAT, FacStaffAl_Fee FLOAT, Guest_Fee FLOAT, PRIMARY KEY (Equip_Name));
CREATE TABLE OAG_Employee (Employee_ID INT, Employee_Name VARCHAR(50), Start_Date DATE, End_Date DATE, Position_ID INT, PRIMARY KEY (Employee_ID));
CREATE TABLE Position (Position_ID INT, Position_Descr VARCHAR(50), Position_Salary FLOAT, PRIMARY KEY (Position_ID));
set foreign_key_checks=0;
ALTER TABLE inventory ADD FOREIGN KEY (equip_name) REFERENCES equipment(equip_name);
ALTER TABLE customer ADD FOREIGN KEY (sponsor_ID) REFERENCES customer(sponsor_ID);*/Nope*/
ALTER TABLE signups ADD FOREIGN KEY (customer_ID) REFERENCES customer(customer_ID);
ALTER TABLE signups ADD FOREIGN KEY (Trip_ID) REFERENCES Trip_Instance(Trip_ID); /*nope*/
ALTER TABLE trip_instance ADD FOREIGN KEY (Trip_ID) REFERENCES signups(Trip_ID); /*nope*/
ALTER TABLE trip_instance ADD FOREIGN KEY (Employee_Leader) REFERENCES OAG_Employee(Employee_ID); /*nope*/
ALTER TABLE trip_instance ADD FOREIGN KEY (Employee_Assistant) REFERENCES OAG_Employee(Employee_ID); /*nope*/
ALTER TABLE rental_agreement ADD FOREIGN KEY (customer_id) REFERENCES customer(customer_id);
ALTER TABLE rental_agreement ADD FOREIGN KEY (employee_id) REFERENCES oag_employee(employee_id);
ALTER TABLE rental_detail ADD FOREIGN KEY (item_number) REFERENCES inventory(item_number);
ALTER TABLE rental_detail ADD FOREIGN KEY (agreement_num) REFERENCES rental_agreement(agreement_num); /*nope*/
ALTER TABLE oag_employee ADD FOREIGN KEY (position_ID) REFERENCES `position`(position_ID);`
A foreign key in a table has to point to a primary key in another table, not just part of the primary key. So in your table Signups, the primary key is a composite PK consisting of three columns. The foreign key in another table must have the equivalent three columns included. Generally I think it is better to set a single column PK, then it is easier to define single column FKs in other tables. By all means set a Unique constraint on the three columns if you need that, but have a separate identity column as the PK.
So for the ones it's complaining about:
ALTER TABLE customer ADD FOREIGN KEY (sponsor_ID) REFERENCES customer(sponsor_ID);
*/Nope - FK column is not referencing a PK column. PK column in customer is customer_ID. Is this a self-referencing table? If so then...*/
ALTER TABLE customer ADD FOREIGN KEY (sponsor_ID) REFERENCES customer(customer_ID);
ALTER TABLE signups ADD FOREIGN KEY (Trip_ID) REFERENCES Trip_Instance(Trip_ID);
/*nope - Trip_ID is the PK in table Trip_Type, not Trip_Instance, so...*/
ALTER TABLE signups ADD FOREIGN KEY (Trip_ID) REFERENCES Trip_Type(Trip_ID);
ALTER TABLE trip_instance ADD FOREIGN KEY (Trip_ID) REFERENCES signups(Trip_ID);
/*nope - same as above, so...*/
ALTER TABLE trip_instance ADD FOREIGN KEY (Trip_ID) REFERENCES Trip_Type(Trip_ID);
ALTER TABLE trip_instance ADD FOREIGN KEY (Employee_Leader) REFERENCES OAG_Employee(Employee_ID);
ALTER TABLE trip_instance ADD FOREIGN KEY (Employee_Assistant) REFERENCES OAG_Employee(Employee_ID);
/*nope - for both of these the FK column is varchar(50) but the referenced column is INT. What you should have is...*/
CREATE TABLE Trip_Instance (Instance_ID INT, Trip_Date DATE, Trip_ID INT, Employee_Leader_ID INT, Employee_assistant_ID INT,PRIMARY KEY(Instance_ID, Trip_ID));
ALTER TABLE trip_instance ADD FOREIGN KEY (Employee_Leader_ID) REFERENCES OAG_Employee(Employee_ID); /*nope*/
ALTER TABLE trip_instance ADD FOREIGN KEY (Employee_Assistant_ID) REFERENCES OAG_Employee(Employee_ID);
Hope this helps.