Constraint situation.

The following problem has a long history.
In the past I have asked quetions about this.
Parent table
PK_id
removed_indication
the other fields
Child table
CK_id
removed_indication
PK_id
parent_removed indication
the other fields
Constraints we want to implement.
1. A child should have a parent.
This is just a FK PK relationship
2. If a parent has children (not removed) then the parent can not be
removed.
(Remark 1: A child can be removed at any time.
Remark 2: Removal is done by changing the removed indication.)
Is it possible to implement this as a constriant.
We prefer not to use triggers or business logic.
At the end of this message the create script for the two tables and
constraint 1.
thanks for your time,
ben brugman
CREATE TABLE [dbo].[Parent1] (
[PK_id] [int] NOT NULL ,
[removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[child1] (
[CK_id] [int] NULL ,
[removed_indication] [int] NULL ,
[PK_id] [int] NULL ,
[parent_removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
(
[PK_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[child1] ADD
CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
(
[PK_id]
) REFERENCES [dbo].[Parent1] (
[PK_id]
)
GO"ben brugman" <ben@.niethier.nl> wrote in message
news:eSEEy$DjEHA.1184@.TK2MSFTNGP12.phx.gbl...
> The following problem has a long history.
> In the past I have asked quetions about this.
> Parent table
> PK_id
> removed_indication
> the other fields
> Child table
> CK_id
> removed_indication
> PK_id
> parent_removed indication
> the other fields
> Constraints we want to implement.
> 1. A child should have a parent.
> This is just a FK PK relationship
> 2. If a parent has children (not removed) then the parent can not be
> removed.
> (Remark 1: A child can be removed at any time.
> Remark 2: Removal is done by changing the removed indication.)
> Is it possible to implement this as a constriant.
> We prefer not to use triggers or business logic.
> At the end of this message the create script for the two tables and
> constraint 1.
>
Yes it is possible. The trick is that if any column in the foreign key side
of the constraint is null, then the constraint is not enforced. SO make the
relationship on PK_ID and removed_indication. Then to detach a child, set
removed_indication to null. You may want to rename the column since it's
valid values are 0 and NULL, not 1 and 0, but you get the idea.
Anyway I have to mention that the most obvious way to detach a child row is
by simply nulling it's PK_ID, but I suppose you have your reasons.
Here's an altered script.
David
drop table child1
drop table parent1
go
CREATE TABLE [dbo].[Parent1] (
[PK_id] [int] NOT NULL ,
[removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[child1] (
[CK_id] [int] NULL ,
[removed_indication] [int] NULL ,
[PK_id] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
(
[PK_id]
) ON [PRIMARY]
alter table parent1
add constraint ck_parent1_removed_incication
check (removed_indication = 0 or removed_indication is null)
GO
ALTER TABLE [dbo].[Parent1] ADD
CONSTRAINT [UK_Parent1]
UNIQUE(pk_id,removed_indication)
go
ALTER TABLE [dbo].[child1] ADD
CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
(
PK_id,removed_indication
) REFERENCES [dbo].[Parent1] (
PK_id,removed_indication
)
GO
alter table child1
add constraint ck__child1_removed_incication
check (removed_indication = 0 or removed_indication is null)
go
insert into parent1 (pk_id,removed_indication) values(1,0)
insert into child1 (ck_id,pk_id,removed_indication) values(1,1,0)
go
--will fail with fk violation
delete from parent1 where pk_id = 1
--detaches the child row by nulling part of the fk constraint
update child1 set removed_indication = null
where pk_id = 1
--now this will work, even though the child still exists
delete from parent1 where pk_id = 1|||Thanks for your attention and your solution.
Is your solution a reasonable solution, because
the column is used to join on as well. (parent join child).
And (in the future) it is used to search on as well ?
(Find all removed children).
My question actually is : Is this a trick, is this a good practise
or commonly used ?
ben
> Yes it is possible. The trick is that if any column in the foreign key
side
> of the constraint is null, then the constraint is not enforced. SO make
the
> relationship on PK_ID and removed_indication. Then to detach a child, set
> removed_indication to null. You may want to rename the column since it's
> valid values are 0 and NULL, not 1 and 0, but you get the idea.
> Anyway I have to mention that the most obvious way to detach a child row
is
> by simply nulling it's PK_ID, but I suppose you have your reasons.
(Yes the same reason why we are not simply deleting the child).
> Here's an altered script.
> David
> drop table child1
> drop table parent1
> go
> CREATE TABLE [dbo].[Parent1] (
> [PK_id] [int] NOT NULL ,
> [removed_indication] [int] NULL ,
> [information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[child1] (
> [CK_id] [int] NULL ,
> [removed_indication] [int] NULL ,
> [PK_id] [int] NULL ,
> [information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
> CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
> (
> [PK_id]
> ) ON [PRIMARY]
> alter table parent1
> add constraint ck_parent1_removed_incication
> check (removed_indication = 0 or removed_indication is null)
> GO
> ALTER TABLE [dbo].[Parent1] ADD
> CONSTRAINT [UK_Parent1]
> UNIQUE(pk_id,removed_indication)
> go
> ALTER TABLE [dbo].[child1] ADD
> CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
> (
> PK_id,removed_indication
> ) REFERENCES [dbo].[Parent1] (
> PK_id,removed_indication
> )
> GO
> alter table child1
> add constraint ck__child1_removed_incication
> check (removed_indication = 0 or removed_indication is null)
> go
> insert into parent1 (pk_id,removed_indication) values(1,0)
> insert into child1 (ck_id,pk_id,removed_indication) values(1,1,0)
> go
> --will fail with fk violation
> delete from parent1 where pk_id = 1
> --detaches the child row by nulling part of the fk constraint
> update child1 set removed_indication = null
> where pk_id = 1
>
> --now this will work, even though the child still exists
> delete from parent1 where pk_id = 1
>
>|||"ben brugman" <ben@.niethier.nl> wrote in message
news:ehUgXUEjEHA.2812@.tk2msftngp13.phx.gbl...
> Thanks for your attention and your solution.
> Is your solution a reasonable solution, because
> the column is used to join on as well. (parent join child).
> And (in the future) it is used to search on as well ?
> (Find all removed children).
As always you should support the FK with an index on child1.
create index ix_fk_child1 on child1(pk_id,removed_indicator)
this index will support searching and simplify locking.
> My question actually is : Is this a trick, is this a good practise
> or commonly used ?
It should work fine, but as I mentioned it would be more common to simply
make child1.pk_id nullable and null it out to detach a child1 row from a
parent1 row. You may have some special need to retain the relationship
information in memo form, and therefore to introduce another column simply
to disable the relationship.
David