Delete all records from a database except a user

Asked

Viewed 428 times

1

I have a database that I need to delete all records that exist in it, except one user that is in the table User linked to another table (Aspnetusers). I’ve made a few attempts to SSMS, but always run into problems of reference and conflicts.

For example, I’ve run the following command:

USE [dbTeste]
GO

DELETE FROM [dbo].[AspNetUsers]
  WHERE Id !='52252ba2-8312-4650-b829-c611e2c3cfdb'
  GO

Message 547, Level 16, Status 0, Line 4 The DELETE statement conflicted with the REFERENCE Constraint "Fk_dbo.Usuario_dbo.Aspnetusers_applicationuser_id". The Conflict occurred in database "matina", table "dbo.Usuario", column 'Applicationuser_id'. The statement has been terminated.

I just need to keep this user, which I use to log in and delete all records from the other tables.

sql sql-server ssms

  • Friend, this error refers to other tables that receive the PK of this user, to erase the users, it will need to erase the PK records of the users.

1 answer

0


You can use the script of this OS response to generate key changes to include ON DELETE CASCADE which will automatically delete the line with the foreign key set when the primary key of the table being referenced is deleted.

SELECT DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; ',
       CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + '].[' + ForeignKeys.ForeignTableName + '] WITH CHECK ADD CONSTRAINT [' +  ForeignKeys.ForeignKeyName + '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn + ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id) + '].[' + sys.objects.name + ']([' + sys.columns.name + ']) ON DELETE CASCADE; '
  FROM sys.objects
 INNER JOIN sys.columns ON sys.columns.object_id = sys.objects.object_id
 INNER JOIN (
   SELECT sys.foreign_keys.name as ForeignKeyName,
          SCHEMA_NAME(sys.objects.schema_id) as ForeignTableSchema,
          sys.objects.name as ForeignTableName,
          sys.columns.name  as ForeignTableColumn,
          sys.foreign_keys.referenced_object_id as referenced_object_id,
          sys.foreign_key_columns.referenced_column_id as referenced_column_id,
     FROM sys.foreign_keys
    INNER JOIN sys.foreign_key_columns ON sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id
    INNER JOIN sys.objects ON sys.objects.object_id = sys.foreign_keys.parent_object_id
    INNER JOIN sys.columns ON sys.columns.object_id = sys.objects.object_id
                          AND sys.columns.column_id = sys.foreign_key_columns.parent_column_id
 ) ForeignKeys ON ForeignKeys.referenced_object_id = sys.objects.object_id
              AND ForeignKeys.referenced_column_id = sys.columns.column_id
 WHERE sys.objects.type = 'U'
   AND sys.objects.name NOT IN ('sysdiagrams')
   AND sys.objects.name = 'AspNetUsers'

Heed to the fact that the query above does not support composite keys.

After executing the change scripts it should be possible to execute the DELETE the way you mentioned.

Browser other questions tagged sql sql-server ssms

You are not signed in. Login or sign up in order to post.