Delete from FILETABLE with foreign key constraint

Question

Background

I'm looking into creating a simple web app, a part of which will display Images associated with Items. I've decided to look into using the FILETABLE feature of SQL Server which will allow binary image data to be uploaded into the exposed share directly. As such there is a use case to allow the deletion of files (rows in a FILETABLE) through Windows Explorer. This example replicates the issue, which stems from having a foreign key relationship to a FILETABLE.

Structure

Having already added an image using File Explorer to the FILETABLE with the path_locator of 0xFF5354649088A1EFEE8F747CD11030F80800170620:

CREATE TABLE [dbo].[Image] AS FILETABLE WITH (FileTable_Directory = 'Images');
GO
CREATE TABLE [dbo].[ImageLink] (
     [id] INT NOT NULL IDENTITY(1, 1)
    ,[path_locator] HIERARCHYID NOT NULL
    ,FOREIGN KEY ([path_locator]) REFERENCES [dbo].[Image] ([path_locator])
);
GO
INSERT INTO [dbo].[ImageLink] ([path_locator]) VALUES (0xFF5354649088A1EFEE8F747CD11030F80800170620);

Issue

Upon deleting the file through File Explorer...

... the file disappears from the directory as Windows reports the deletion a success but the row is not removed from the FILETABLE.

However, when trying to delete through SQL Server, the familiar reference constraint conflict error is thrown:

DELETE FROM [dbo].[Image] WHERE [path_locator] = 0xFF5354649088A1EFEE8F747CD11030F80800170620;

Msg 547, Level 16, State 0, Line 69
The DELETE statement conflicted with the REFERENCE constraint "FK__ImageLink__path___5070F446". The conflict occurred in database "FileTableTest", table "dbo.ImageLink", column 'path_locator'.

I added an AFTER DELETE trigger to the FILETABLE with the intention of removed the referencing row, but this also does not get executed.

Question

• How might I go about propagating the delete through the link table upon deletion through Windows Explorer?
• Is there some kind of SQL Server/Windows API hook I can detect and execute DML code that handles the delete?

Update #1

From BOL, the following section kind of confirms the behaviour, although doesn't offer any further information.

Transactional Semantics

When you access the files in a FileTable by using file I/O APIs, these operations are not associated with any user transactions, and have the following additional characteristics:

• Since non-transacted access to FILESTREAM data in a FileTable is not associated with any transaction, it does not have any specific isolation semantics. However SQL Server may use internal transactions to enforce locking or concurrency semantics on the FileTable data. Any internal transactions of this type are done with read-committed isolation.

Answer 1

The problem is the foreign key.

Use 'ON CASCADE DELETE' in your foreign key, so when you delete through File Explorer the associated ImageLink is deleted too.