Making sure every parent has a child - constraints

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Making sure every parent has a child - constraints

Author

Topic

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-02 : 14:11:47

hello there,

I think I might know the answer to this, but I wanted to see if any one has come up with a slick idea for enforcing this relationship. If I have two tables and one is dependent on the other (parent-child relationship), how can I enforce that every parent record has a corresponding child record? Here is a code example

[CODE]
USE tempdb

GO

CREATE TABLE dbo.Parent
(
ParentId int NOT NULL
)

ALTER TABLE dbo.Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentId)

CREATE TABLE dbo.Child
(
ParentId int NOT NULL
,ChildId int NOT NULL
)

ALTER TABLE dbo.Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ParentId, ChildId)

ALTER TABLE dbo.Child ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentId)
REFERENCES dbo.Parent(ParentId)

INSERT INTO dbo.Parent VALUES (1)
INSERT INTO dbo.Child VALUES (1,1)
INSERT INTO dbo.Parent VALUES (2)
INSERT INTO dbo.Child VALUES (2,2)
INSERT INTO dbo.Child VALUES (2,1)
INSERT INTO dbo.Parent VALUES (3)

SELECT
p.ParentId, 'I SHOULD HAVE A DEPENDENT RECORD'
FROM
dbo.Parent p
LEFT JOIN
dbo.Child c
ON
p.ParentId = c.ParentId
WHERE c.ParentId IS NULL

[/CODE]

ParentId 3 should have a child record associated with it. I am assuming that these are my choices:

1) code all inserts to the parent table along with a insert to the child table and wrap those in a transaction

2) place a trigger for insert on the parent table that ensures that the child table is populated after data for the parent.

Here is the gotcha, we will be using a middle-tier data access layer (nhibernate or dlink) so .NET application developers will be creating the data modifications at the transactional level. Also there might be several ongoing ETLs that populate this schema as well, so multiple points of entry and seperate code blocks. I don't want to hide business logic within triggers.

I assume that all our coders are competent and could enforce this properly via code, but I know that mistakes happen. Has any one come across this situation and have a solution for enforcing the integrity of the schema with constraints?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 14:31:30

because there are two records each dependant on the other, there would have to be a transaction around the two inserts in order to enforce that real-time circular constraint. assuming you are unable to guarantee they are transactionally inserted, would an hourly/nightly clean-up process be acceptable? an alternative to a trigger could be a check constraint using a function, but that doesn't seem to help your dilemma

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-02 : 15:28:11

How would the constraint with a udf work?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 15:41:15

you could write a scalar value function that return the number of children for a given parent, then add a check constraint to the table like...

ALTER TABLE [dbo].[parent] WITH CHECK ADD CONSTRAINT [has_children] CHECK (([dbo].[children_count](parentid)>(0)))

but it still requires there be a transaction around the two table inserts and performance is likely poor

maassql
Starting Member

13 Posts

Posted - 2009-09-16 : 18:42:08

I know this post was a loooong time ago, but I felt like I just wanted to weigh in for posterity's sake. Mainly because I saw the solution as so very much different. Neither requires anything complicated - triggers, constraints, transactions to enforce the rules that I read.

What I read & inferred from above was the following:
* Before a parent / child relationship exists, an entity (person) exists.
* A parent does not exist, unless A child exists
* A parent may have multiple children
* A child must have a parent

Here is how I imagined modeling the above:
Person
=======
person_id
~properties that are true w/o regard for the relationship
~properties that are min / max ( Favorite Child - aka - Max Liked Child)

parent_child_relationship
=======
parent_person_id
child_person_id ( unique index ? )
~properties at the relationship level

OR---------------------------

person_relationship
=======
person_id_1
person_id_2
person_relationship_type_id
~properties at the relationship level

person_relationship_type
=========
1 - person_id_1 is a parent of person_id_2, person_id_2 is a child of person_id_1
2 - person_id_1 is married to person_id_2 or person_id_2 is married to person_id_1

Anything will give up its secrets if you love it enough. -- George Washington Carver

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources