Delete orphan row avoiding FK table scan

Question

Delete orphan row avoiding FK table scan

vmoreau

SSC Enthusiast

Points: 118
More actions
March 11, 2014 at 2:27 pm

#283314

Hi,
In a SQL 2018 database I need to fix some data in a huge common table that is referenced by FK across lots of other huge table in the database.
- I need to delete about 30000 orphan row (100% sure there is no reference to it)
- All referenced table do not have index on that column
Is there a way withoud disabling/dropping FK on all tables to speed up the query and prevent the check on all those table during the delete as I know it wont violate the data integrity.
Thanks
Vincent

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323753 More actions · Answer 1

vmoreau (3/11/2014)
Hi,
In a SQL 2018 database I need to fix some data in a huge common table that is referenced by FK across lots of other huge table in the database.
- I need to delete about 30000 orphan row (100% sure there is no reference to it)
- All referenced table do not have index on that column
Is there a way withoud disabling/dropping FK on all tables to speed up the query and prevent the check on all those table during the delete as I know it wont violate the data integrity.
Thanks
Vincent

when you say "orphaned", i'm assuming you must mean the opposite; some data needs to be deleted, but you are afraid FK's might prevent the data from being deleted.

if it was truly orphaned, then the foreign keys were already disabled, and that's not what i think you are saying.

is there an index on the foreign key column? that would ensure the data is found fastest, and the locking is for the minimal amount of time. you could create one and drop the index afterwards if need be.

say i KNEW i wanted to remove ParentID 15 from ALL tables that used to reference it, but i THINK, only ONE table currently references it.

it's easy to EXEC sp_fkeys ParentTable, and turn that information into "SELECT * FROM ChildTable Where ParentId = 15" for every reference, to confirm there's no data referencing that key (15);

so is that what you are essentially asking? how to 1. speed up the delete (index) and 2. make sure the key is not referenced anywhere else, so you don't need to disable FKs?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vmoreau SSC Enthusiast Points: 118 More actions · Answer 2

Hi,

Thanks for helping, I migh better explain with an example

CREATE TABLE MyTableA
(
MyTableAID int IDENTITY(1,1) NOT NULL,
MyTableTextCol varchar(max) NOT NULL,
TypeCol varchar(20) NOT NULL,
CONSTRAINT PK_MyTableA PRIMARY KEY CLUSTERED(MyTableAID)
)
CREATE TABLE MyTableBx
(
MyTableIDBx [int] IDENTITY(1,1) NOT NULL,
SomeCol varchar(max) NOT NULL,
MyTableAID int null CONSTRAINT FK_MyTableBx_MyTableA FOREIGN KEY REFERENCES MyTableA(MyTableAID),
CONSTRAINT [PK_MyTableA] PRIMARY KEY CLUSTERED(MyTableIDBx)
)

Lots of table look like MyTableBx and I am 100% certain that all data of query below are not referenced in any of MyTableBx so if i perform

delete * from MyTableA where TypeCol = 'NO_PARENT'

it will not violate FK but since MyTableBx.MyTableAID have no index this will produce a clustered index scan on all MyTableBx and will take forever to complete 😉

Usually nobody need to delete this table, but found a 16gb bug :w00t: a I need to reclaim that space 🙂

The only option I found yet is to delete a small quantity via a off hours job 🙁 But in case there is an alternative I didnt know 😀

thanks