How to delete users from database?

Question

I make test in Laravel, and I want to truncate all users, and create only one. This code show me error SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint. What can I do that truncate users ? Just I don't want to use solutions that violate database settings, e.g.Schema::disableForeignKeyConstraints();

 public function test_delete_user()
    {
 
        DB::table('users')->truncate();
        $role = User::factory()->create();
     .......
        
    }

You would have to remove those records in which those users are set as value for the foreign key, first. — CBroe, Mar 07 '23 at 09:13
Would [Resetting The Database After Each Test](https://laravel.com/docs/10.x/database-testing#resetting-the-database-after-each-test) help? — brombeer, Mar 07 '23 at 09:26
Before tuncating user tabel you muset need to delete relational records or trunctare table that user Foreign Key of user table — Sagar Sainkar, Mar 07 '23 at 09:34
As in your example code you are not using models then you have to use same query for all Foreign Key tables from user table before truncating user table DB::table('table_name')->truncate(); @perh22 — Sagar Sainkar, Mar 07 '23 at 09:46

score -1 · Answer 1 · answered Mar 07 '23 at 10:48

-1

Your table user have a forign key constraint remove that to get truncate working.

Can i see the database schema.

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

This is for MysQl try that on your table

answered Mar 07 '23 at 10:48

John

1
1

Or try to find how to do that on the type of your database – John Mar 07 '23 at 10:51

score -1 · Answer 2 · answered Mar 07 '23 at 11:28

-1

Before using

DB::table('users')->truncate();

You may try to use (suppose your foreign table name "addresses")

DB::statement('ALTER TABLE addresses DROP FOREIGN KEY addresses_user_id_foreign');

It may help you. Then it may look like

public function test_delete_user()
    {
        DB::statement('ALTER TABLE addresses DROP FOREIGN KEY addresses_user_id_foreign');
 
        DB::table('users')->truncate();
        $role = User::factory()->create();
     .......
        
    }

answered Mar 07 '23 at 11:28

Azizur Rahman

14
2

if you don't want to use solutions that violate database settings, then - at first, you may update other table foreign_key_id value to 1 - export the table with data - delete all data from the table - then you may delete all data from user table - now you may create your first data by id 1 (which will use as foreign key by other table) - now you may import exported data to your other table. I think it will work, because it worked for me. – Azizur Rahman Mar 07 '23 at 22:13

How to delete users from database?

2 Answers2