Skip to content
Advertisement

How to design tables with foreign key but value is NULL in MYSQL?

I am trying to design a database with 2 tables. Project and Student.

Student

| StudentID | ProjectID |
|-----------|-----------|
| 1         | NULL      |
| 2         | 1         |

Project

| ProjectID | Info      |
|-----------|-----------|
| 1         | some info |
| 2         | some info |


Student table records what project each student selects, and the student could only select a project that exists in the project table.

However, when a student first enrolls in the class, I want to insert his info into the student table but at that time, he did not pick any project yet so the ProjectID would be NULL.

This is not allowed when I make Student - ProjectID as a foreign key because NULL does not exist in Project.

What should I do?

Advertisement

Answer

Simply declare a foreign key on a nullable column:

create table students (
    . . . 
    projectID int,   -- nullable by default
    constraint fk_students_project foreign key (projectID) references projects(projectID)
);

Of course, you need to create the projects table first, so the foreign key declaration is valid.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement