1

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.

Ilya
  • 4,583
  • 4
  • 26
  • 51
josef
  • 89
  • 2
  • 9
  • You should use `ON DELETE CASCADE` [Check out this response.](http://stackoverflow.com/a/13444888/4269347) – Petter Jul 07 '16 at 09:46
  • on delete cascade will remove automatically the tasks depend of project deleted! and i don't want to remove the tasks when i remove project – josef Jul 07 '16 at 09:52
  • So what do you want to do? Whats expected to happen with the task table data for that project? – jarlh Jul 07 '16 at 09:54
  • i want to delete a project and keep the tasks which depends on the project and does not remove it – josef Jul 07 '16 at 09:56
  • 1
    I'd suggest another approach. Add an is_active column to the projects table. Set to inactive instead of deleting. – jarlh Jul 07 '16 at 10:02
  • Which DBMS are you using? –  Jul 07 '16 at 10:31
  • Please edit your table definitions into your question. – philipxy Jul 10 '16 at 00:52

2 Answers2

1

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.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
0

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.)

philipxy
  • 14,867
  • 6
  • 39
  • 83