-1

I have several tables with content. Is there a way how I can remove all the content without removing the keys (primary key, foreign key etc.) ?

When I say truncate or delete it obviously returns an error.

Cannot truncate table 'login' because it is being referenced by a FOREIGN KEY constraint.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jamie
  • 10,302
  • 32
  • 103
  • 186
  • What do you want to do with that foreign key constraint? Maybe you should truncate the table referencing this one before truncating this one? – David Jun 30 '15 at 17:38
  • possible duplicate of [Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?](http://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint) – Tab Alleman Jun 30 '15 at 17:49

1 Answers1

2

You're right, you cannot run truncate table on an table which uses an foreign key. But you can run a normal delete on the table itself.

The only thing you should take care of is the order.

For example: You have a table users and a table users_log where you store all users logins. You cannot run the delete on the users table if still rows from users_log referencing them. If you delete all rows from users_log and afterwards deleting the rows from users everything should be fine.

In this particular example, this code will work:

DELETE FROM users_log
DELETE FROM users

While this won work:

DELETE FROM users
DELETE FROM users_log -- which has a foreign key constraint on users
Ionic
  • 3,884
  • 1
  • 12
  • 33