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.