Why am I getting "Snapshot isolation transaction aborted due to update conflict"?
When deleting from the parent table, SQL Server must check for the existence of any FK child rows that refer to that row. When there is no suitable child index, this check performs a full scan of the child table:



If the scan encounters a row that has been modified since the delete command's snapshot transaction started, it will fail with an update conflict (by definition). A full scan will obviously touch every row in the table.

With a suitable index, SQL Server can locate and test just the rows in the child table that could match the to-be-deleted parent. When these particular rows have not been modified, no update conflict occurs:



Note that foreign key checks under row versioning isolation levels take shared locks (for correctness) as well as detecting update conflicts. For example, the internal hints on the child table accesses above are:


PhyOp_Range TBL: [dbo].[Child]
    Hints( READ-COMMITTEDLOCK FORCEDINDEX DETECT-SNAPSHOT-CONFLICT )


Sadly this is not currently exposed in execution plans.

Related articles of mine:


The SNAPSHOT Isolation Level
Data Modifications under Read Committed Snapshot Isolation