DAT/380 Week 3 LAB 3.12 – Create LessonSchedule table with FK constraints

Description

DAT/380 Week 3 LAB 3.12 – Create LessonSchedule table with FK constraints

Two tables are created:
Horse with columns:
ID – integer, primary key
RegisteredName – variable-length string

Student with columns:
ID – integer, primary key
FirstName – variable-length string
LastName – variable-length string

Create the LessonSchedule table with columns:
HorseID – integer with range 0 to 65 thousand, not NULL, partial primary key, foreign key references Horse(ID)
StudentID – integer with range 0 to 65 thousand, foreign key references Student(ID)
LessonDateTime – date/time, not NULL, partial primary key

If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically.

If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.

Note: Table and column names are case sensitive in the auto-grader.

CREATE TABLE Horse (
ID SMALLINT UNSIGNED AUTO_INCREMENT,
RegisteredName VARCHAR(15),
PRIMARY KEY (ID)
);

CREATE TABLE Student (
ID SMALLINT UNSIGNED AUTO_INCREMENT,
FirstName VARCHAR(20),
LastName VARCHAR(30),
PRIMARY KEY (ID)
);

— Your SQL statements go here