January 6, 2020 at 4:15 pm
I am providing DDL to re-create the parent and child tables involved and the DDL is a representation of the actual 2 tables involved because I'm not allowed to post the source DDL so I altered the table names and column names so I could. That said, this is a general purpose question that is applicable to any 2 tables with a parent/child relationship.
I've always understood that when you have a parent and child table where the Parent tables primary key is referenced by the child tables foreign key then the DB system should disallow orphaned rows, rows that exist in the Child table for which there is no matching row in the parent table. If you had a DB for hotel management you wouldn't want it to allow the ROOM table to have a row for a room that is not linked to a row in the FLOOR table or the BUILDING table
Assuming the included DDL how can the delete statement (included in the DDL) ever delete anything? Shouldn't the constraint between the parent tables PK and the Child tables FK make it impossible for there to ever be any rows in the CHILD table that the below could delete? In my mind the CHILD_TABLE should never have any rows of data where hPTCol is Null and yet if I read the DELETE correctly that is what its looking for. What is it that I'm not seeing/understanding correctly that allows for the DELETE to delete any rows?
NOTE: The DELETE includes additional hard coded conditions like iType = 1 because the real DELETE statement includes these and I wanted to make sure my re-creation was an accurate representation of what I'm dealing with.
DELETE FROM CHILD_TABLE
WHERE hPTCol IN( 100 )
AND NOT EXISTS ( SELECT * FROM PARENT_TABLE WHERE hPKCol = CHILD_TABLE.hPTCol AND iType = 1)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE dbo.PARENT_TABLE ( hPKCol NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
iType NUMERIC(18,0) NULL,
sName VARCHAR(255) NULL,
sAddr2 VARCHAR(255) NULL,
sCity VARCHAR(40) NULL,
sState VARCHAR(5) NULL,
sZipCode VARCHAR(30) NULL,
CONSTRAINT [PK_PARENT_TABLE] PRIMARY KEY CLUSTERED ( hPKCol ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE dbo.CHILD_TABLE( hPTCol NUMERIC (18,0) NOT NULL,
dtPeriod DATETIME NOT NULL,
iFinBook INT NOT NULL CONSTRAINT [DC_CHILD_TABLE_iFinBook] DEFAULT ((0)),
hGLAccount NUMERIC (18,0) NOT NULL,
sPeriodBegin NUMERIC (21,2) NULL,
sPeriodActivity NUMERIC (21,2) NULL,
sPeriodBeginBudget NUMERIC (21,2) NULL,
sBudget NUMERIC (21,2) NULL,
CONSTRAINT [PK_CHILD_TABLE] PRIMARY KEY CLUSTERED ( hPTCol ASC,
dtPeriod ASC,
iFinBook ASC,
hGLAccount ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE dbo.CHILD_TABLE WITH NOCHECK ADD CONSTRAINT [FK_ACCT_CHILD_TABLE] FOREIGN KEY(hGLAccount)
REFERENCES dbo.ACCT (hMy)
GO
ALTER TABLE dbo.CHILD_TABLE NOCHECK CONSTRAINT [FK_ACCT_CHILD_TABLE]
GO
ALTER TABLE dbo.CHILD_TABLE WITH NOCHECK ADD CONSTRAINT [FK_PARENT_TABLE_CHILD_TABLE] FOREIGN KEY(hPTCol)
REFERENCES dbo.PARENT_TABLE (hPKCol)
GO
ALTER TABLE dbo.CHILD_TABLE NOCHECK CONSTRAINT [FK_PARENT_TABLE_CHILD_TABLE]
GO
Kindest Regards,
Just say No to Facebook!January 6, 2020 at 4:26 pm
I think you have two choices: (1) you can't delete any row in the parent table while related rows exist in the child table, or (2) you create the foreign key constraint with cascading delete, so that deleting a row in the parent table also deletes all related rows in the child table.
John
January 6, 2020 at 4:31 pm
Your SQL gives errors:
Msg 1767, Level 16, State 0, Line 42
Foreign key 'FK_ACCT_CHILD_TABLE' references invalid table 'dbo.ACCT'.
Msg 1750, Level 16, State 0, Line 42
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 46
Constraint 'FK_ACCT_CHILD_TABLE' does not exist.
Msg 4916, Level 16, State 0, Line 46
Could not enable or disable the constraint. See previous errors.
The DELETE statement:
DELETE ct
FROM CHILD_TABLE ct
WHERE ct.hPTCol IN( 100 )
AND NOT EXISTS (SELECT *
FROM PARENT_TABLE pt
WHERE pt.hPKCol = ct.hPTCol
AND pt.iType = 1)
Is deleting child rows that don't relate to a parent row that has iType = 1
So any rows on the PARENT_TABLE that have an iType other than 1 will have their CHILD_TABLE rows deleted.
January 6, 2020 at 4:34 pm
John
Assuming I understand correctly you are in agreement with me in that assuming the setup of the 2 tables I've shown the included DELETE should never find anything to delete because the constraint between the 2 tables disallows for there to be a row in the CHILD_TABLE that is an orphan. Correct? I'm being told by our cloud vendor that this exact DELETE found and removed over a thousand rows of data and I simply can't understand how that's possible based on the constraint between the 2 tables. To the best of my knowledge that goes against the whole point of the constraint which is to prevent orphan rows in the child table.
With the DB being the cloud we have limited access to it. We are currently waiting on a copy of the DB for end of year archival. When we get I plan to restore it locally and test this for myself but in the mean time I wanted to ask on these forums about this just in case I was missing something. It wouldn't be the first time that this cloud vendor has had a disconnect between the front line support (those who we as clients deal with) and the people whop work with the DB's. IO was up until close to 5AM on 2020/01/01 because our cloud based DB ran into problems trying to close the accounting books for the 2019 year and the answer provided to us was that there were orphan rows in the CHILD table of a pair of very important Parent-Child tables in the DB and the answer simply did not reconcile with me; the delete statement they used simply made no sense.
Thank you for taking the time to respond.
Kindest Regards,
Just say No to Facebook!January 6, 2020 at 4:47 pm
Yes, the constraint prevents orphaned rows in the child table - in other words it stops children having their parent deleted. What it doesn't do is stop a parent having its children deleted. Therefore the foreign key constraint isn't going to prevent DELETE statements from removing rows from the child table, although I think the WHERE clause would - that DELETE statement is never going to delete any rows if the constraint is in place. (However, your constraints are created with the NOCHECK option, so that's going to stop them working in any case, I think.)
John
January 6, 2020 at 5:15 pm
John Roberts- Not sure about the errors, I don't get these when I use any of the code I've included. The 2 CREATE TABLE statements and the DELETE all work.
Thanks
Kindest Regards,
Just say No to Facebook!January 6, 2020 at 5:23 pm
John Roberts- Not sure about the errors, I don't get these when I use any of the code I've included. The 2 CREATE TABLE statements and the DELETE all work.Thanks
Maybe you have a table named ACCT with a column named hMy?
ALTER TABLE dbo.CHILD_TABLE WITH NOCHECK ADD CONSTRAINT [FK_ACCT_CHILD_TABLE] FOREIGN KEY(hGLAccount)
REFERENCES dbo.ACCT (hMy)
January 6, 2020 at 6:45 pm
Why do you have the nocheck statements in there?
Is it that currently turned off on your actual tables? Or is it a setting someone would have toggled on and off? That will prevent the FK from working and will not revalidate the data when if it's turned back on.
January 6, 2020 at 7:25 pm
I used the Script to New Window feature in version of SQL Mgt Studio we have access to in the vendors cloud to quickly generate the DDL. I changed only the table name and column names and left everything else.
Kindest Regards,
Just say No to Facebook!January 6, 2020 at 7:35 pm
That's it. Its difficult to modify DDL and get everything. I'd rather post the original/unaltered but can't do it.
There is an ACCT table and just like the CHILD_TABLE if you try to delete a row from the ACCT table that a row in the CHILD_TABLE points to via hGLAcct you get a violation because you'd have orphaned records. Since I created these 2 dummy tables in the same DB that ACT table exists and doesn't error out for me.
At this point I'm comfortable with my initial assumption being correct. Once I get a copy of the DB and can restore it locally and thus have unrestricted access I will find out the truth of this by converting the DELETE from into a SELECT and if it returns any data then that will be an interesting investigation but I suspect it will not. The vendors DB people told their support (who we deal with) that over a thousand rows were deleted.
Thanks for taking time to reply.
Kindest Regards,
Just say No to Facebook!January 6, 2020 at 7:50 pm
I used the Script to New Window feature in version of SQL Mgt Studio we have access to in the vendors cloud to quickly generate the DDL. I changed only the table name and column names and left everything else.
Then it sounds like the constraints are not on and that will certainly allow orphaned records.
January 7, 2020 at 3:17 am
>> I've always understood that when you have a parent [referenced] and child table where the parent [referenced] tables primary key is referenced by the child tables foreign key then the DB system should disallow orphaned rows, rows that exist in the Child table for which there is no matching row in the parent [referenced] table. <<
Not quite. In RDBMS we do not talk about "parent" and "child" tables; those terms come from networked databases. We use the concept of referenced and referencing tables, which are derived from the data modeling concepts of weak and strong entities. A weak entity depends on the existence of a strong entity, such as order details have to be details of an order, so the order has to exist before they do.
>> If you had a DB for hotel management you wouldn't want it to allow the ROOM table to have a row for a room that is not linked to a row in the FLOOR table or the BUILDING table <<
You're quite right. We would not allow these tables to exist at all! The entity of a room (clearly a physical entity) would have the attributes of (building_id, floor_nbr, room_nbr). They would never be in separate tables! You have a problem telling the difference between an entity, an attribute of the entity, and the value of an attribute.
Here is the skeleton DRI for preventing orphans in the first place:
CREATE TABLE Strong_stuff
(strong_id ??? NOT NULL PRIMARY KEY,
..);
CREATE TABLE Weak_stuff
(weak_id ?? NOT NULL,
strong_id ??? NOT NULL
REFERENCES Strong_stuff ,
ON DELETE CASCADE,
PRIMARY KEY (weak_id, strong_id)
..);
Please post DDL and follow ANSI/ISO standards when asking for help.
January 7, 2020 at 3:27 pm
I'm pretty sure Jonathan AC Roberts has is right in his original post, the iType = 1 in the NOT EXISTS subquery is what's throwing you.
You're correct that the code as you provided (ignoring the NOCHECK issue) will not allow a child row that does not join to a valid parent row. However, a child row that joins to a parent row where iType = 2 will be deleted by the delete statement at the top of your code as the NOT EXISTS subquery will not return a row. Hopefully that makes sense.
January 7, 2020 at 7:32 pm
Chris
I knew there was something about that damned statement that bothered me but I could not see it (i.e. forest for the tress). I was soo hung up on the part about how there could even be any orphaned rows in the CHILD table that the part about iType = 2 resulting in an untended deletion went right by me. Thankfully in our case the data in the CHILD table is easily recreated so any data that was deleted even unintentionally can be re-created. Whats most important is that the vendors support people executed a DELETE that did something that it was not designed to do. They are under strict orders not execute any data changing code without our reviewing it but when your in the cloud you are at the mercy of the cloud master.
I'm still waiting on the DB backup but now I'm very much looking forward to testing on it once I have it and proving my suspicions.
Thank you all for your help .
Kindest Regards,
Just say No to Facebook!January 9, 2020 at 4:25 pm
I've been through this and NOCHECK is the issue - I took this out and couldn't put my child data back
so I created the child data without the foreign keys and then did a "delete from childdata where column not in (select column x from parenttable)" then re-built the keys
you can even use the output clause to record the bad records to a table for investigation
MVDBA
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply