0

I have a SQL table called "user" and a table "login" that has a foreign key constraint to a user. I want to be able to delete a row in the user table, even if there are login rows that reference it. Right now the database stops me from doing this.

Does anyone know how I can alter the table (through SQL or preferably through PHPmyAdmin to allow me to do this?

The tables were created automatically through Django.

Edit: To clarify: I don't want to cascade the delete. That is, I want the rows in the Login table to remain even though the user they reference is gone.

user2662692
  • 197
  • 3
  • 15
  • http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html, see dropping foreign key section – koriander Feb 27 '15 at 20:26
  • 2
    You use a foreign key and you ask why does it behaves like a foreign key...Why do you need constraint integrity if you dont care about it?Just drop the foreign key altogether. – Mihai Feb 27 '15 at 20:29
  • @Mihai I was under the impression that there were other benefits to foreign keys than just enforcing this constraint. Is that true? – user2662692 Feb 27 '15 at 20:29
  • 1
    not really,a foreign key creates an index on that column but thats about it.The question on my mind why do you need to keep logins with users which dont exist anymore?If you want a history of users of some sort use a trigger to dump the deleted users in a history table – Mihai Feb 27 '15 at 20:31
  • @Mihai My app is very data driven. There is a side where you look at charts and graphs that tell you about user activity. Here there are graphs that show logins over the past month or year. I would prefer that the logins stay on this graph even after the user is deleted. – user2662692 Feb 27 '15 at 20:38
  • 1
    OK,so how many users do you have?Why do you think you need to delete them? – Mihai Feb 27 '15 at 20:38
  • @Mihai I would rather also not add a delete field on my User table. Then every time I query the table i would have to do WHERE user.deleted=False – user2662692 Feb 27 '15 at 20:38
  • @Mihai just in case users no longer want to use the site and want to delete their account. There are thousands of users. – user2662692 Feb 27 '15 at 20:39
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/71909/discussion-between-user2662692-and-mihai). – user2662692 Feb 27 '15 at 20:39

2 Answers2

0

If you want this kind of behavior you have to create the foreign key with an ON DELETE CASCADE clause. With an ON DELETE CASCADE foreign key all rows referencing the user will be deleted with the user.

See: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Edit: If you want to keep the user_id in your login tables you just have to drop the foreign key. Anyway, If you are asking this is because you should probably do a logical delete instead of a physical delete: Physical vs. logical / soft delete of database record?

Community
  • 1
  • 1
Jordi Llull
  • 780
  • 5
  • 17
  • Please re read the question. I dont want them to be deleted. I want them to just have the value of the FK of the user which no longer exists. Just the user would be deleted, but the rows with the FK would remain. – user2662692 Feb 27 '15 at 20:23
  • Either you want referential integrity, in which case the referenced records **must** exist, or you don't care. Pick one. You can't have referential integrity without the database being consistent. – tadman Feb 27 '15 at 20:35
  • 1
    Sorry I didn't understand you the first time. I edited the answer giving some additional information. – Jordi Llull Feb 27 '15 at 20:37
  • @JordiLlull Thanks! The problem is I have hundreds of querys to the table and I would rather not do WHERE deleted=False in every query. – user2662692 Feb 27 '15 at 20:42
0

Proper way to do this is to mark offending users as "inactive" so they can't login and you still maintain referential integrity of your database.

Deleting data from master table that has referential integrity links to some data in slave table is bad praxis.

ivo.tisljar
  • 69
  • 8