-1

My referenced tables aren't opening even though I have coded them. I am in the correct database and I have created the tables I need. I ran the show tables command and only two of my tables show up. I have also checked that I am using the correct database, I still can't find out how to make my tables show up.

Here is my code for reference:

BEGIN;
/* Create Employees table */
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
EmployeeDateOfHire DATE,
EmployeeAddress VARCHAR(100),
EmployeePhone VARCHAR(15),
ClassAssignment VARCHAR(50)
) ENGINE=INNODB;

/* Create Attendance table /
CREATE TABLE Attendance (
Attendance_ID INT PRIMARY KEY,
Attendance_Date DATE,
Attendance_Status VARCHAR(10),
Student_ID INT,
FOREIGN KEY (Student_ID) REFERENCES Students(STUDENT_ID)
) ENGINE=INNODB;
/ Create Tuition table */

CREATE TABLE Tuition (
TuitionID INT PRIMARY KEY,
StudentID INT,
InvoiceDate DATE,
Amount DECIMAL(10, 2),
Pmt_Status VARCHAR(20),
PaidAmount DECIMAL(10, 2),
FOREIGN KEY (StudentID) REFERENCES Students(Student_ID)
) ENGINE=INNODB;

/* Enroll_Checklist table */
CREATE TABLE Enroll_Checklist (
Enroll_Checklist_ID INT,
Student_info_sheet VARCHAR(45),
Student_medical_sheet VARCHAR(45),
Authorized_pickup_sheet VARCHAR(45),
Student_birth_certificate VARCHAR(45),
Social_security_card CHAR(12),
Immunization_record VARCHAR(45),
Registration_fee DECIMAL(10, 2),
Student_ID INT,
Employee_ID INT
);

/* Create Authorized_pickup table */

CREATE TABLE Authorized_pickup (
Authorized_pickup_ID INT,
Authorized_pickup_1 VARCHAR(45),
Authorized_pickup_2 VARCHAR(45),
Authorized_pickup_3 VARCHAR(45),
STUDENT_ID INT
);

/* CREATE PARENT_GUARDIAN TABLE*/

CREATE TABLE PARENT_GUARDIAN(
PG_ID INT PRIMARY KEY,
PG_FNAME VARCHAR(35) NOT NULL,
PG_LNAME VARCHAR(35) NOT NULL,
PG_PHONE_NUM CHAR(14) NOT NULL,
PG_ADDRESS VARCHAR(45) NOT NULL,
PG_CITY VARCHAR(20) NOT NULL,
PG_STATE CHAR(2) NOT NULL,
PG_ZIPCode CHAR(5) NOT NULL,
PG_RELATIONSHIP_STUDENT VARCHAR(35) NOT NULL
);

/* CREATE CLASS TABLE*/
CREATE TABLE CLASS (
CLASS_ID INT NOT NULL PRIMARY KEY,
Class_Name VARCHAR(25) NOT NULL,
Age_Group INT NOT NULL,
Room_Num INT NOT NULL,
Room_ID INT NOT NULL,
FOREIGN KEY(Room_ID) REFERENCES Classroom(Room_ID)
);

/* CREATE CLASSROOM TABLE*/

CREATE TABLE Classroom (
Room_ID INT NOT NULL PRIMARY KEY,
Room_Type VARCHAR(20) NOT NULL,
Capacity INT NOT NULL
);

CREATE TABLE STUDENT (
STUDENT_ID INT PRIMARY KEY,
STUDENT_FNAME VARCHAR(25) NOT NULL,
STUDENT_LNAME VARCHAR(25) NOT NULL,
STUDENT_DOB DATE NOT NULL,
STUDENT_ADDRESS VARCHAR(50) NOT NULL,
STUDENT_CITY VARCHAR(20) NOT NULL,
STUDENT_STATE CHAR(2) NOT NULL,
STUDENT_ZIPCode CHAR(5) NOT NULL,
STUDENT_START_DATE DATE,
STUDENT_PHONE_NUM CHAR(14) NOT NULL,
STUDENT_ALLERGIES VARCHAR(50) NOT NULL,
STUDENT_TUITION_RATE DECIMAL(10, 2) NOT NULL,
CLASS_ID INT,
FOREIGN KEY(CLASS_ID) REFERENCES CLASS(CLASS_ID)
) ENGINE=INNODB;

/* CREATE RELATIONSHIP TABLE*/
CREATE TABLE RELATIONSHIP (
RELATIONSHIP_ID INT PRIMARY KEY NOT NULL,
STUDENT

I have tried specifying the engine, I have tried running each table code separately, and I have tried to show each table separately but I still keep getting the error message 1824: failed to open the referenced table. I am quite new to mysql and I don't know what else to try.

Ken White
  • 123,280
  • 14
  • 225
  • 444
strawbeary
  • 1
  • 1
    Which line is getting that error? – Barmar Apr 13 '23 at 20:09
  • 2
    `FOREIGN KEY (StudentID) REFERENCES Students(Student_ID)` There's no `Students` table. There's a `STUDENT` table, but it's defined later, the referenced table has to be defined first. – Barmar Apr 13 '23 at 20:11
  • 1
    `FOREIGN KEY(Room_ID) REFERENCES Classroom(Room_ID)` The `Classroom` table is defined after this. – Barmar Apr 13 '23 at 20:11
  • 1
    So fix 2 problems: Get the table names right, and fix the order so the referenced table is first. – Barmar Apr 13 '23 at 20:12
  • You can also leave the foreign keys out of the table definitions. Then add them all at the end with `ALTER TABLE`. – Barmar Apr 13 '23 at 20:13
  • @Barmar Reordering the tables worked. Thank you. – strawbeary Apr 13 '23 at 21:13

1 Answers1

0

So here is the issue:

In this table Tuition you used a foreign key of Students table. But while creating the table there is not Students table present at that moment. So to run this you have to set the parameter SET FOREIGN_KEY_CHECKS = 0; at the top of the query to stop MySQL to check the Foreign key.

Here is a sample:

/* Stoping MySql to check foreign key */
SET FOREIGN_KEY_CHECKS = 0;

/* Create Employees table */
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
EmployeeDateOfHire DATE,
EmployeeAddress VARCHAR(100),
EmployeePhone VARCHAR(15),
ClassAssignment VARCHAR(50)
) ENGINE=INNODB;

/* Create Attendance table /
CREATE TABLE Attendance (
Attendance_ID INT PRIMARY KEY,
Attendance_Date DATE,
Attendance_Status VARCHAR(10),
Student_ID INT,
FOREIGN KEY (Student_ID) REFERENCES Students(STUDENT_ID)
) ENGINE=INNODB;
/ Create Tuition table */

CREATE TABLE Tuition (
TuitionID INT PRIMARY KEY,
StudentID INT,
InvoiceDate DATE,
Amount DECIMAL(10, 2),
Pmt_Status VARCHAR(20),
PaidAmount DECIMAL(10, 2),
FOREIGN KEY (StudentID) REFERENCES Students(Student_ID)
) ENGINE=INNODB;

/* Enroll_Checklist table */
CREATE TABLE Enroll_Checklist (
Enroll_Checklist_ID INT,
Student_info_sheet VARCHAR(45),
Student_medical_sheet VARCHAR(45),
Authorized_pickup_sheet VARCHAR(45),
Student_birth_certificate VARCHAR(45),
Social_security_card CHAR(12),
Immunization_record VARCHAR(45),
Registration_fee DECIMAL(10, 2),
Student_ID INT,
Employee_ID INT
);

/* Create Authorized_pickup table */

CREATE TABLE Authorized_pickup (
Authorized_pickup_ID INT,
Authorized_pickup_1 VARCHAR(45),
Authorized_pickup_2 VARCHAR(45),
Authorized_pickup_3 VARCHAR(45),
STUDENT_ID INT
);

/* CREATE PARENT_GUARDIAN TABLE*/

CREATE TABLE PARENT_GUARDIAN(
PG_ID INT PRIMARY KEY,
PG_FNAME VARCHAR(35) NOT NULL,
PG_LNAME VARCHAR(35) NOT NULL,
PG_PHONE_NUM CHAR(14) NOT NULL,
PG_ADDRESS VARCHAR(45) NOT NULL,
PG_CITY VARCHAR(20) NOT NULL,
PG_STATE CHAR(2) NOT NULL,
PG_ZIPCode CHAR(5) NOT NULL,
PG_RELATIONSHIP_STUDENT VARCHAR(35) NOT NULL
);

/* CREATE CLASS TABLE*/
CREATE TABLE CLASS (
CLASS_ID INT NOT NULL PRIMARY KEY,
Class_Name VARCHAR(25) NOT NULL,
Age_Group INT NOT NULL,
Room_Num INT NOT NULL,
Room_ID INT NOT NULL,
FOREIGN KEY(Room_ID) REFERENCES Classroom(Room_ID)
);

/* CREATE CLASSROOM TABLE*/

CREATE TABLE Classroom (
Room_ID INT NOT NULL PRIMARY KEY,
Room_Type VARCHAR(20) NOT NULL,
Capacity INT NOT NULL
);

CREATE TABLE STUDENT (
STUDENT_ID INT PRIMARY KEY,
STUDENT_FNAME VARCHAR(25) NOT NULL,
STUDENT_LNAME VARCHAR(25) NOT NULL,
STUDENT_DOB DATE NOT NULL,
STUDENT_ADDRESS VARCHAR(50) NOT NULL,
STUDENT_CITY VARCHAR(20) NOT NULL,
STUDENT_STATE CHAR(2) NOT NULL,
STUDENT_ZIPCode CHAR(5) NOT NULL,
STUDENT_START_DATE DATE,
STUDENT_PHONE_NUM CHAR(14) NOT NULL,
STUDENT_ALLERGIES VARCHAR(50) NOT NULL,
STUDENT_TUITION_RATE DECIMAL(10, 2) NOT NULL,
CLASS_ID INT,
FOREIGN KEY(CLASS_ID) REFERENCES CLASS(CLASS_ID)
) ENGINE=INNODB;

/* Enabling MySql to check foreign key again */
SET FOREIGN_KEY_CHECKS = 1;
Renuka Jatoth
  • 46
  • 2