-1

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?

Harold
  • 373
  • 2
  • 12
  • Normally you would have a table of students, a table of projects, and a table relating one to the other – Strawberry Mar 01 '20 at 19:42
  • "This is not allowed" Why do you think that? Please read about how SQL FKs work. Please do reasonable research including the manual & goolging before considering asking a question. Please don't ask duplicate questions. See [ask], other [help] links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Mar 01 '20 at 20:45

2 Answers2

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

MySQLs does allow a NULL value on a column that has a foreign key on it.

If you get an error message it is likely the column was declared NOT NULL or something like the NULL value was submitted as varchar

Can table columns with a Foreign Key be NULL?

Roland Kreuzer
  • 912
  • 4
  • 11