CODEWITHSUNDEEP
sqlforeign-keys
josef
josef

Reputation: 89

Delete and foreign key constraint SQL

I have a project table has a primary key "projectID", each project has tasks then in my task table that has a primary key "taskID", I have the foreign key "ProjetID".

I want to know if it is possible that if I delete a project, the tasks carried out in this project remains in the task table and will not be removed.

Upvotes: 1

Views: 116

Answers (2)

philipxy
philipxy

Reputation: 15118

A FOREIGN KEY declaration tells the DBMS that a non-NULL value for a subrow for its columns must also appear in the REFERENCES table as a value for a subrow for its columns. If that's what you want, declare it. If that's not what you want, don't.

You say that Task has FOREIGN KEY Task (projectID) REFEFERENCES Project (projectID). But then you contradictorily say that you want a projectID value in Task even if it isn't a projectID value in Project. Because if that's what you want then you do not want the foreign key. So don't declare it.

We do not need to declare foreign key, primary key, unique or other constraints in order to query. They just tell the DBMS that certain things will be true for valid database states. (So it can keep out invalid states and also optimize queries.)

Upvotes: 0

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

You need to set the ProjetID to null before deleting the project. It doesn't make sense to keep it anyway, since you won't find any project anymore.

You could use ON DELETE SET NULL, which is doing exactly this without the need of executing additional SQL statements.

Upvotes: 1

Related Questions