June 13, 2014 at 9:37 am
I'm working on a big, established project which uses a SQL database to store, process and validate the application's data.
- We have many instances of two tables in a parent - child relationship (e.g. parent = an organisation, child = a contract held with that organisation).
- Both parent and child can be soft-deleted via a Deleted column.
- We want to prevent the situation of a soft-deleted parent having undeleted children.
- In line with the rest of the project, this validation needs to be carried out in the database.
- SQL 2005 only!
Here's some DDL which illustrates this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parent](
[ParentID] [int] NOT NULL,
[Deleted] [bit] NOT NULL,
[Notes] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
[ParentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child](
[ChildID] [int] NOT NULL,
[Deleted] [bit] NOT NULL,
[ParentID] [int] NOT NULL,
[Notes] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
[ChildID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Child] WITH CHECK ADD CONSTRAINT [FK_Child_Parent] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Parent] ([ParentID])
GO
ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK_Child_Parent]
GO
Insert Into Parent (ParentID, Deleted, Notes)
Select 10, 0, 'OK'
Union All
Select 11, 0, 'OK'
Union All
Select 12, 1, 'Shouldn''t be allowed!'
Union All
Select 13, 1, 'OK because child is also deleted'
Insert Into Child (ChildID, Deleted, ParentID, Notes)
Select 20, 0, 10, 'OK'
Union All
Select 21, 1, 11, 'OK - only I''m deleted'
Union All
Select 22, 0, 12, 'Shouldn''t be allowed!'
Union All
Select 23, 1, 13, 'OK because parent is also deleted'
Select *
From Parent P
Join Child C On (C.ParentID = P.ParentID)
I'm sure this must be a pretty common scenario and I was wondering what people's thoughts were on the best way to implement the constraint. I can think of:
1) Validation in the stored procedures used to maintain data in the two tables.
2) Validation in the two tables' triggers (should there be such things!)
3) Foreign key based validation (requiring a calculated column on each table).
4) A unique index on a schemabound view covering both tables (and including a doubler in order to make invalid rows not unique).
5) A third table, containing ParentID, ChildID, ParentDeleted and ChildDeleted columns with a check constraint.
We've just done some refactoring work to replace the one approach we'd been using for several years with a different and (hopefully) better one. But I'm not saying which at this stage!
Any thoughts, opinions, suggestions would be most welcome.
June 13, 2014 at 9:44 am
Why do you have a Parent and Child in different tables? Should I assume that it's not a hierarchy but a header-detail?
Could you include the deleted column on your PK and FK? That will save you lots of code and SQL Server will do everything for you. You might want to add a UNIQUE constraint on your current PK to prevent duplicates as well.
June 13, 2014 at 9:54 am
Sorry, I should have said. Yes, each is a header - detail type of arrangement. In my example of "parent = an organisation, child = a contract held with that organisation" the organisation is a real-life thing as is the contract, each having their own, different details.
I'm not sure I see how simply putting the Deleted column in my PK and FK would work - wouldn't you need a bit more than that, as outlined in "3) Foreign key based validation (requiring a calculated column on each table)"? Or am I missing the bleedin' obvious?
June 13, 2014 at 10:02 am
julian.fletcher (6/13/2014)
Sorry, I should have said. Yes, each is a header - detail type of arrangement. In my example of "parent = an organisation, child = a contract held with that organisation" the organisation is a real-life thing as is the contract, each having their own, different details.I'm not sure I see how simply putting the Deleted column in my PK and FK would work - wouldn't you need a bit more than that, as outlined in "3) Foreign key based validation (requiring a calculated column on each table)"? Or am I missing the bleedin' obvious?
I agree with Luis. This seems like a great approach. It would require two steps. The first is to add the deleted column to your PK on the Organization table. You would of course have to add a ParentDeleted column or something like that in your Contract table. Set up that relationship to cascade on update.
The next step would be to add a unique constraint to ParentID in the Organization table.
This approach should solve the issue quite nicely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 13, 2014 at 12:16 pm
Thinking again, I might suggest a trigger approach on the case that you can soft-delete contracts with out soft-deleting the organisation. Using the FK approach, you wouldn't be able to do it.
Be sure to make a trigger that allows multiple rows to be updated.
June 13, 2014 at 12:22 pm
Luis Cazares (6/13/2014)
Thinking again, I might suggest a trigger approach on the case that you can soft-delete contracts with out soft-deleting the organisation. Using the FK approach, you wouldn't be able to do it.Be sure to make a trigger that allows multiple rows to be updated.
Sure you can. You just need two columns ParentDeleted and ChildDeleted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 16, 2014 at 10:12 am
Well the approach we have taken is an FK-based one. (Previously, we had used triggers.)
We have a calculated column on the parent table which is normally equal to the PK, but equal to minus the PK if the record is deleted. (So it remains unique and not null.)
And we have a calculated column on the child table which is normally equal to the ParentID, but equal to null of the record is deleted.
Then we have a FK from the calculated column on the child table to the calculated column on the parent table. This allows all combinations of deleted on both tables with the exception of having a deleted parent record but an undeleted child record - which is exactly the validation we require.
(This approach also needs a unique index on the calculated column on the parent table. We also have one on the calculated column on the child table for performance.)
It seems to work, but it does seem a bit of a palaver. So I was wondering if there was a better (i.e. simpler) approach. Perhaps not!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply