Performing SQL Query to Remove Unused Users from a Database

Question

I'm currently working with a database that consists of a users table, a permissions table, a set of documents-related tables, and several miscellaneous tables that have foreign key dependencies on rows in the user table.

I'm trying to remove all user entries from the 'Users' table that meet the following criteria:

Not referenced by an entry in one of the documents tables.
Not referenced by an entry in the permissions table.
Contains a null value in the 'Customer ID' column of the User row.

I'm able to create a query that gets all users, which looks like this:

SELECT id
INTO MyTableVar
FROM Users
WHERE
    (NOT EXISTS (SELECT Author_Id FROM ItemInstances_DocumentInstance 
                 WHERE Users.Id = ItemInstances_DocumentInstance.Author_Id)
    AND NOT EXISTS (SELECT CompletedBy_Id FROM TaskInstanceUser 
                    WHERE Users.Id = TaskInstanceUser.CompletedBy_Id)
    AND Cust_Id IS NULL
    AND Id > 4)

SELECT * 
FROM MyTableVar

This query gets all of Id's of users that I want to remove, but I get an error when I try to delete these entries

The DELETE statement conflicted with the REFERENCE constraint "FK_MessageUser_User.

I'm stumped as to how I should use the ID's I've queried to remove entries in the MessageUser_User table that correspond to users I want to delete. I feel like this should be easy, but I can't figure out a way to do it with SQL syntax.

PS: I'd also appreciate some feedback on how I wrote what I have so far for my query. I'd love to know what I could do to make it cleaner. I'm new to SQL and need all the help I can get.

`delete from MessageUser_User where exists(select 1 from MyTableVar where id = userid)` (or you can use inner join) — Igor, Mar 14 '19 at 19:54

Thermos · Answer 1 · 2019-03-15T19:44:11.613

I'm guessing that the table with the Foreign Key does not have ON DELETE CASCADE which you can read about here.

If you have the ability to alter constraints on your table, you can do this, which will permit the referencing table to automatically delete records that reference a deleted row from the main table.

ALTER TABLE MessageUser_User DROP 
   CONSTRAINT FK_MessageUser_User;

ALTER TABLE MessageUser_User ADD 
   CONSTRAINT FK_MessageUser_User
      FOREIGN KEY (<<IdColumnName>>)
      REFERENCES Users (Id)
      ON DELETE CASCADE;

Otherwise, you can use a separate query to delete from MessageUser_User where it contains the IDs you want to delete in it's foreign key column:

DELETE FROM MessageUser_User WHERE ID IN (SELECT ID FROM MyTableVar );

Regarding the style of your delete query - I usually prefer to do left joins then delete the records where there is a null in the right table(s):

SELECT id
INTO MyTableVar
FROM Users
LEFT JOIN ItemInstances_DocumentInstance ON Author_Id = Users.Id
LEFT JOIN TastInstanceUser ON CompletedBy_Id = Users.Id
WHERE
Author_Id IS NULL
AND CompletedBy_Id IS NULL
AND Cust_Id IS NULL
AND Id > 4

Performing SQL Query to Remove Unused Users from a Database

1 Answers1