Deleting rows from a table is a pretty simple task right? Not always. Foreign keys, while providing a ton of benefits, do make deletes a bit more complicated.
-- Demo setup
CREATE TABLE Authors (
Author_Id INT NOT NULL IDENTITY (1,1)
CONSTRAINT pk_Author PRIMARY KEY
,Name varchar(100)
);
CREATE TABLE Books (
Book_Id INT NOT NULL IDENTITY (1,1)
CONSTRAINT pk_Books PRIMARY KEY
,Author_Id INT
CONSTRAINT fk_Books_Author_Id FOREIGN KEY
REFERENCES Authors(Author_Id)
,Name varchar(100)
);
INSERT INTO Authors VALUES
('Itzik Ben-Gan')
,('Grant Fritchey')
,('Kellyn Pot’Vin-Gorman');
INSERT INTO Books VALUES
(1,'T-SQL Fundamentals')
,(1,'T-SQL Window Functions: For data analysis and beyond (Developer Reference)')
,(1,'Exam Ref 70-761 Querying Data with Transact-SQL')
,(2,'SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance')
,(2,'SQL Server Query Performance Tuning')
,(2,'SQL Server Execution Plans')
,(3,'Crushing the IT Gender Bias: Thriving as a Woman in Technology');
Now let’s say that I need to delete all of Grant’s books for whatever reason. He’s tired of SQL Server, want’s his name removed from it completely, and decided to take up butterfly farming or something.
DELETE FROM Authors WHERE name = 'Grant Fritchey';
DELETE FROM Books WHERE name IN ('SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance'
,'SQL Server Query Performance Tuning'
,'SQL Server Execution Plans');
Msg 547, Level 16, State 0, Line 34
The DELETE statement conflicted with the REFERENCE constraint “fk_Books_Author_Id”. The conflict occurred in database “WorkplaceInventory”, table “dbo.Books”, column ‘Author_Id’.
The statement has been terminated.
(3 rows affected)
Well, at least the rows from Books were deleted, and I could run it again and the row from Authors would also be deleted. Which I might do if this was a one-off and this simple (i.e. only two tables involved and no other code). But, if you are writing this code to be re-usable (in case Grant keeps changing his mind), then you really need this to work without flaws. In fact the inspiration for this particular post was a piece of code to be used for rolling back a production install that involves inserting some rows into a few tables. One of our requirements is that they run without flaw, for obvious reasons.
The first option (and not one I’d chose) is to either disable or drop the foreign key.
-- Disable
ALTER TABLE Books NOCHECK CONSTRAINT fk_Books_Author_Id;
-- or Delete
ALTER TABLE Books DROP CONSTRAINT fk_Books_Author_Id;
Then afterwards we add them back.
-- Enable
ALTER TABLE Books CHECK CONSTRAINT fk_Books_Author_Id;
-- or Add
ALTER TABLE Books ADD CONSTRAINT fk_Books_Author_Id FOREIGN KEY (Author_Id)
REFERENCES Author(Author_Id);
The down side here is that when you add the constraint back (or enable it) it has to validate the data and for a large table that can take some time, and if you made any mistakes with your delete, i.e. leaving any child rows behind, it’s going to fail.
My preference here is to just flip the order of your deletes. Which sounds really simple, but you’d be surprised how often people don’t think of it. Basically we want to make sure we delete all of the children first, then the parent.
-- Always delete the children first.
DELETE FROM Books WHERE name IN ('SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance'
,'SQL Server Query Performance Tuning'
,'SQL Server Execution Plans');
DELETE FROM Authors WHERE name = 'Grant Fritchey';
Again, this is a pretty simple example, but try thinking about it when you have a whole series of foreign keys. Say half a dozen, or even more, of them. All of a sudden, if you aren’t familiar with the structure of your database it could take some time to get that order right. Make sure you do though, as I said above, we want these to run without fail.
For those of you who are thinking, what about cascading deletes? Turn those on on your foreign key and then you can just delete the parent and all of the children go automatically. And that is true, but I’m very leery of cascading deletes. On one notable occasion we had someone delete one row from a table. Only when the delete finished several minutes later did they realize they’d gotten rid of over a third of the database.