Implementing a Business Rule

SQL Apprentice Question
I need to implement a business rule. Here is the original DDL:

create table parent(parentID int not null,
ValidFrom datetime not null, ValidTo datetime not null)
go
create table child(childID int not null, parentID int not null,
ValidFrom datetime not null, ValidTo datetime not null)


Besides the obvious RI constraint, I need to make sure that the parent
row is valid for all the time the child one is valid:


(parent.ValidFrom <= child.ValidFrom) and
(child.ValidFrom < child.ValidTo) and
(child.ValidTo <= parent.ValidTo)


Here is my approach. It works, but at a rather steep price of adding
two 8 byte datetime columns (smalldatetime does not have enough
precision) on the child table and two composite 3-column indexes. I was
wondering if there is a cheaper approach, and I am in a very big hurry.
I am sure I'm not the first person to encounter such a problem, and I d
not want to reinvent the wheel, but I ahve a very limited time for
research. Thanks in advance for the suggestions!


drop table child
drop table parent
go
create table parent(parentID int not null,
ValidFrom datetime not null, ValidTo datetime not null)
go
alter table parent add constraint parentPK primary key(parentID)
go
alter table parent add constraint parentUK1 unique(parentID, ValidFrom,
ValidTo)
go
--alter table parent add constraint parentU2 unique(parentID, ValidTo)
go
alter table parent add constraint parentC1 check(ValidFrom < ValidTo)
go
set nocount on
insert parent values(1, '20060101','21000101')
go
create table child(childID int not null, parentID int not null,
ValidFrom datetime not null, ValidTo datetime not null,
parentValidFrom datetime not null, parentValidTo datetime not null)
go
alter table child add constraint childPK primary key(childID)
go
alter table child add constraint childFK1 foreign key(parentID,
parentValidFrom, parentValidTo)
references parent(parentID, ValidFrom, ValidTo) on update cascade
go
--alter table child add constraint childFK2 foreign key(parentID,
parentValidTo)
-- references parent(parentID, ValidTo) on update cascade
go
alter table child add constraint childC1 check((parentValidFrom <=
ValidFrom)
and (ValidFrom < ValidTo) and (ValidTo <= parentValidTo))
go
set nocount on
select 'next batch must succeed'
go
set nocount off
insert into child(childID, parentID, ValidFrom, ValidTo,
parentValidFrom, parentValidTo)
select 1, 1, '20060115', '21000101', '20060101', '21000101'
go
set nocount on
select 'next insert must fail (check)'
go
set nocount off
insert into child(childID, parentID, ValidFrom, ValidTo,
parentValidFrom, parentValidTo)
select 2, 1, '20050115', '21000101', '20060101', '21000101'
go
set nocount on
select 'next insert must fail (FK)'
go
set nocount off
insert into child(childID, parentID, ValidFrom, ValidTo,
parentValidFrom, parentValidTo)
select 2, 1, '20060115', '21000101', '20060103', '21000101'
go
set nocount on
select 'next update must fail (check)'
go
set nocount off
update parent set ValidTo = '20060819' where parentID=1
go
set nocount on
select 'next batch must succeed'
go
set nocount off
update child set ValidTo = '20060818' where parentID=1
update parent set ValidTo = '20060819' where parentID=1
go
select * from parent
select * from child



Celko Answers
Here is my guess at "the obvious RI constraints", and a little
clean up on the data element names.

CREATE TABLE Parents
(parent_id INTEGER NOT NULL PRIMARY KEY,
from_date DATETIME NOT NULL,
to_date DATETIME NOT NULL,
CHECK (from_date < to_date));


CREATE TABLE Children
(parent_id INTEGER NOT NULL
REFERENCES Parents (parent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
child_id INTEGER NOT NULL,
from_date DATETIME NOT NULL,
to_date DATETIME NOT NULL,
CHECK (from_date < to_date),
PRIMARY KEY (parent_id, child_id));



>> I need to make sure that the parent row is valid for all the time the child one is valid:


Try an updatable VIEW with a CHECK OPTION

CREATE VIEW ValidParents (parent_id, from_date, to_date
AS
SELECT P.parent_id, P.from_date, P.to_date
FROM Parents AS P
WHERE NOT EXISTS
(SELECT *
FROM Children AS C
WHERE C.parent_id = P.parent_id
AND (C.from_date NOT BETWEEN P.from_date
AND P.to_date
OR C.to_date NOT BETWEEN P.from_date
AND P.to_date)
WITH CHECK OPTION;


You can turn it "inside out" to get a valid children VIEW, too.


CREATE VIEW ValidChildren (parent_id, child_id, from_date, to_date
AS
SELECT P.parent_id, child_id, P.from_date, P.to_date
FROM Children AS C
WHERE NOT EXISTS
(SELECT *
FROM Parents AS P
WHERE C.parent_id = P.parent_id
AND (C.from_date NOT BETWEEN P.from_date
AND P.to_date
OR C.to_date NOT BETWEEN P.from_date
AND P.to_date)
WITH CHECK OPTION;


Now do all the updates via the views.


And I would rather be using CREATE ASSERTION instead :)