CODEWITHSUNDEEP
mysql
softshell turtle
softshell turtle

Reputation: 3

MySQL - any way to set FOREIGN KEY to NULL?

I am making a database for the gym at school and there is 'Open Gym' which does not have an Instructor, however you would register for the open gym as you would any other class. It makes sense that a FK would not be able to be NULL in that it would be a PK in the parent table, but if there is any way around this, any help would be great.

Not sure if I should make an extra table just for the open gym sessions, not sure if that is the most ideal way of going about this.

CREATE TABLE `instructor` (
`InstructorID` VARCHAR(50) NOT NULL,
`First Name` VARCHAR(50) DEFAULT NULL,
`Last Name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (InstructorID)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `class` (
`ClassID` DECIMAL(10,0) NOT NULL,
`Name` VARCHAR(50) DEFAULT NULL,
`Type` VARCHAR(50) DEFAULT NULL,
`Discount Price` DECIMAL(10,0) DEFAULT NULL,
`Regular Price` DECIMAL(10,0) DEFAULT NULL,
`InstructorID` VARCHAR(50) DEFAULT NULL,
`UnitID` DECIMAL(10,0) DEFAULT NULL,
`CourseID` DECIMAL(10,0) NOT NULL,
PRIMARY KEY (ClassID),
FOREIGN KEY (UnitID) REFERENCES subunit(UnitID),
FOREIGN KEY (InstructorID) REFERENCES instructor(InstructorID),
FOREIGN KEY (CourseID) REFERENCES course(CourseID)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

Upvotes: 0

Views: 66

Answers (2)

Beginner
Beginner

Reputation: 4153

I see the cause of your problem because you put an empty value which is '' instead of null

INSERT INTO Class VALUES ('10067','Beach Volleyball','Recreational','0','0','','000010','0006');

change '' to null

Upvotes: 0

Hans Z
Hans Z

Reputation: 4744

If you want the type of data consistency benefits of having a foreign key for the "Open Gym" class that you have for every other class, you can add a dummy row in the instructor table.

Otherwise, if you make instructor.InstructorId nullable that will also work

Upvotes: 1

Related Questions