August 24, 2015 at 2:41 pm
I'm trying to delete some records from some tables in a SQL Server 2008 R2 database. There's a foreign key relationship between the two tables. To make things easier here's the definition of both tables:
-- Parent table
CREATE TABLE [dbo].[PharmInvInItemPackages](
[InventoryInDetailID] [int] IDENTITY(1,1) NOT NULL,
[InventoryInID] [int] NOT NULL,
[ItemPackageID] [int] NOT NULL,
[LotNum] [nvarchar](20) NULL,
[ItemCost] [float] NULL CONSTRAINT [DF__Temporary__ItemC__1273C1CD] DEFAULT ((0)),
[ItemDescr] [nvarchar](60) NULL,
[ItemNDCNum] [nvarchar](20) NULL,
[QtyPerPackage] [float] NULL CONSTRAINT [DF_PharmInventoryInItems_QtyPerPackage] DEFAULT ((1)),
[QtyReceived] [float] NULL CONSTRAINT [DF_PharmInventoryInItems_QtyPackagesReceived] DEFAULT ((0)),
[TotalCost] [money] NULL CONSTRAINT [DF__Temporary__Total__1367E606] DEFAULT ((0)),
[LotExpiredYN] [bit] NOT NULL CONSTRAINT [DF__Temporary__LotEx__145C0A3F] DEFAULT ((0)),
[LotExpirationDate] [datetime] NULL,
[ShelfLocationID] [int] NULL,
[Is340B_YN] [bit] NULL CONSTRAINT [DF_PharmInventoryInItems_Is340B_YN] DEFAULT ((0)),
[LabelledYN] [bit] NULL CONSTRAINT [DF_PharmInventoryInItems_LabelledYN] DEFAULT ((0)),
[QtyRequestedForIssue] [float] NULL CONSTRAINT [DF_PharmInventoryInItems_QtyPackagesRequestedForIssue] DEFAULT ((0)),
[Notes] [nvarchar](50) NULL,
[DateEntered] [datetime] NULL CONSTRAINT [DF__Temporary__DateE__15502E78] DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(0))),
[DateModified] [datetime] NULL,
[DateTimeStamp] [timestamp] NULL,
CONSTRAINT [aaaaaPharmInventoryInItems_PK] PRIMARY KEY NONCLUSTERED
(
[InventoryInDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
-- Child table
CREATE TABLE [dbo].[PharmInvOutItemPackageReceipts](
[InvOutItemPackageReceiptID] [int] IDENTITY(114615,1) NOT NULL,
[InvOutItemLineID] [int] NOT NULL,
[InventoryInDetailID] [int] NOT NULL,
[ItemCost] [money] NULL,
[QtyIssuedShippedThisReceipt] [float] NULL,
[TotalCostThisReceipt] [money] NULL,
[ProgramID] [int] NULL,
[IsReceiptItem340B_YN] [bit] NULL,
[ClinicianID] [int] NULL,
[Notes] [nvarchar](50) NULL,
[DateEntered] [datetime] NULL,
[DateModified] [datetime] NULL,
[InventoryOutID] [int] NULL,
[ItemPackageID] [int] NULL,
[DateTimeStamp] [timestamp] NULL,
CONSTRAINT [PK_PharmInvOutItemPackageReceipts] PRIMARY KEY CLUSTERED
(
[InvOutItemPackageReceiptID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
and here's the foreign key definition:
ALTER TABLE [dbo].[PharmInvOutItemPackageReceipts] WITH CHECK ADD CONSTRAINT [FK_PharmInvOutItemPackageReceipts_PharmInvInItemPackages] FOREIGN KEY([InventoryInDetailID])
REFERENCES [dbo].[PharmInvInItemPackages] ([InventoryInDetailID])
GO
ALTER TABLE [dbo].[PharmInvOutItemPackageReceipts] CHECK CONSTRAINT [FK_PharmInvOutItemPackageReceipts_PharmInvInItemPackages]
GO
Here's the DELETE statement I'm trying to execute:
DECLARE @CutOffDate datetime
SET @CutOffDate = '2014-7-1'
PRINT 'Deleting old PharmInvOutItemPackages data (and PharmInvOutItemPackageReceipts data via cascading deletes'
DELETE pp
FROM PharmInvOutItemPackages pp
INNER JOIN PharmInvOutItemPackageReceipts pr
ON pp.[InvOutItemLineID] = pr.[InvOutItemLineID]
WHERE pr.DateEntered < @CutOffDate
PRINT 'Deleting old PharmInvInItemPackages data'
DELETE pp
FROM PharmInvInItemPackages pp
INNER JOIN PharmInvOutItemPackageReceipts pr
ON pp.InventoryInDetailID = pr.InventoryInDetailID
WHERE pr.DateEntered < @CutOffDate
Here's the printout that I get:
Deleting old PharmInvOutItemPackages data (and PharmInvOutItemPackageReceipts data via cascading deletes
(96950 row(s) affected)
Deleting old PharmInvInItemPackages data
(0 row(s) affected)
And here's the error message:
The DELETE statement conflicted with the REFERENCE constraint "FK_PharmInvOutItemPackageReceipts_PharmInvInItemPackages". The conflict occurred in database "PharmacyDB", table "dbo.PharmInvOutItemPackageReceipts", column 'InventoryInDetailID'.
I don't know what I'm doing wrong. I've beaten myself on this for two days. I think this is a case of being too close to the problem to see what I'm doing wrong, so I'd appreciate a fresh set of eyes looking at it, please.
Kindest Regards, Rod Connect with me on LinkedIn.
August 24, 2015 at 2:55 pm
this data is preventing the delete: there's child records in PharmInvOutItemPackageReceipts, that either need to be deleted first or ignored,and delete everything else based on the cutoff date.
SELECT pr.*
--DELETE pr
FROM PharmInvInItemPackages pp
INNER JOIN PharmInvOutItemPackageReceipts pr
ON pp.InventoryInDetailID = pr.InventoryInDetailID
WHERE pr.DateEntered < @CutOffDate
--now that the detail data is cleared, delete the parent data.
SELECT pp.*
--DELETE pp
FROM PharmInvInItemPackages pp
INNER JOIN PharmInvOutItemPackageReceipts pr
ON pp.InventoryInDetailID = pr.InventoryInDetailID
WHERE pr.DateEntered < @CutOffDate
you know your business better. if it went to the point where there's related data in PharmInvOutItemPackageReceipts , can you delete it?
if you just want to delete the stuff that meets the cutoff data, and also does not have detail data, you could do this:
SELECT pp.*
--DELETE pp
FROM PharmInvInItemPackages pp
LEFT JOIN PharmInvOutItemPackageReceipts pr
ON pp.InventoryInDetailID = pr.InventoryInDetailID
WHERE pr.DateEntered < @CutOffDate
AND pr.InventoryInDetailID IS NULL
Lowell
August 25, 2015 at 8:40 am
The weird this is I tried reversing the order - i.e.: deleting the data from PharmInvOutItemPackageReceipts first, but that didn't help. I got the same error message. However you've given me some different parameters to try, which I will.
Kindest Regards, Rod Connect with me on LinkedIn.
August 25, 2015 at 5:21 pm
You should maybe do it the way your comment says - use cascading deletes; change the foreign key to have ON DELETE CASCADE and then use just the single delete statement that deletes the rows in the parent table and the constraint will take care of the child table for you.
A general rule is that if you can do something using schema constraints instead of hand coding, do it with the constraints. But the usual voices will shout that using a constraint to do this is a foul cheating trick, there should be no business logic in the database. :angry:
The problem you are seeing is an extremely common one if you have another process running in parallel that could add rows to the child table, unless you have the two deletes wrapped in the one transaction and were using either REPEATABLE READ or SERIALIZABLE isolation level instead of the default READ COMITTED; using the default isolation level can cause the problem, and so can using AUTOCOMMIT instead of explicit or implicit transactions (since autocommit without any explicit BEGIN TRANSACTION guarantees that the two delete statements will be in separate transactions). Sensible people believe that using an isolation level that risks damaging performance instead of letting constraints be enforced automatically is not really, unless the higher isolation level is unavoidable for other reasons, a good approach - in fact that it's a truly terrible one. 🙂
edit: thinking about (much later) it I'm not sure repeatable read is strong enough isolation for this one; if you don't have the constraint cascade you need serializable.
Tom
August 26, 2015 at 9:17 am
Late yesterday I had the same thought, Tom. i.e.: why not chance the constraint to use cascading deletes? In fact, at least from my point of view that makes more sense. The application is basically an inventory app and database. The Receipts table has 2 foreign key constraints to it, one to the inventory-in table and the other to an inventory-out table. The original developer set it up so that there's a cascading delete between the inventory-out table and the receipts table, but only a check constraint between inventory-in and the receipts table. I admit that I've no experience at all with inventory and warehouses, so my "common sense" may not truly make any sense, but it seems to me that if you thought you'd gotten some inventory, but really didn't that you wouldn't want a receipt about that. My "common sense" says why would you ship something you never had? But it seems as though this system allows them to "ship" imaginary inventory. But I'm really ignorant of inventory/warehouse practice, so maybe it makes sense.
Bottom line, I like you're thinking. Maybe it would be best if we had a cascading delete if either the inventory-in or the inventory-out record is deleted. I'll pass that idea by the BA and see what he says.
Kindest Regards, Rod Connect with me on LinkedIn.
August 26, 2015 at 10:03 am
The foreign key is on the column InventoryInDetailID.
ALTER TABLE [dbo].[PharmInvOutItemPackageReceipts] WITH CHECK ADD CONSTRAINT [FK_PharmInvOutItemPackageReceipts_PharmInvInItemPackages] FOREIGN KEY([InventoryInDetailID])
REFERENCES [dbo].[PharmInvInItemPackages] ([InventoryInDetailID])
You are joining in the delete on the column InvOutItemLineID.
DELETE pp
FROM PharmInvOutItemPackages pp
INNER JOIN PharmInvOutItemPackageReceipts pr
ON pp.[InvOutItemLineID] = pr.[InvOutItemLineID]
WHERE pr.DateEntered < @CutOffDate
So there's no reason to expect that the correct child rows have been deleted because that's not the foreign key column.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply