Best way to enforce constraints between tables inside a database

Question

I am looking for the best way to check the constraints between tables one step ahead of the keys. For example, to check if the value of a child date entry is between the date of the range in the two columns of the parent rows. For example:

Parent table  
ID    DATE_MIN   DATE_MAX
----- ---------- ----------
1     01/01/2009 01/03/2009
...

Child table
PARENT_ID  DATE
---------- ----------
1          01/02/2009
1          01/12/2009   <--- HAVE TO FAIL!
...

I see two approaches:

• Create materialized on-commit views, as shown in this article (or another equivalent on other RDBMSs).
• Use stored procedures and triggers.

Any other approach? Which of the best options?

. " ". , , , . , , . , , " ?". " " .

2. - oracle. , - , any, , .

Answer 1

: Dana the Sane , , DBA.

---

, DBAs ​​, Dana, , 1:1. , , , .

DBA , .

MS Word, , ? , , - .

- - , , . , .

INSERT, UPDATE DELETE . EXECUTE , CRUD . , INSERT (, , ), .

Answer 2

(, , < , foreign key/references) SQL:

create assertion <name> check (<condition>)

- :

create assertion Child_DATE_between_MIN_MAX check (
  not exists (
    select DATE_MIN, DATE, DATE_MAX
      from Parent, Child
     where ID = PARENT_ID
       and DATE < DATE_MIN
       and DATE > DATE_MAX
  )
)

: , <condition> , .

( ), SQL-DBMS ASSERTION.

, , , . , ; .

Lurker , Child.

Damien_The_Unbeliever, , :

1) , /

, ASSERTION (, , SQL-, , ).

, , .. , !

, : ( .pdf , ).

, , , . , :

... , , . The , -, ....

. ​​. , SQL (.pdf) :

1

... SQL, (, , Oracle [Ora99] DB2 [IBM99]) . 8 , SQL'92 , , SQL! , .. , , , ....

, : SQL- ( / ) ASSERTIONs , , .

Answer 3

proc trigger; - .

, , WHERE, :

CREATE FUNCTION CheckFnctn()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = COUNT(*) 
   FROM PARENT
   INNER JOIN CHILD ON PARENT.ID = CHILD.PARENT_ID
   WHERE CHILD.DATE < PARENT.DATE_MIN OR CHILD.DATE > PARENT.DATE_MAX
   RETURN @retval
END;
GO
ALTER TABLE CHILD
ADD CONSTRAINT chkDates CHECK (dbo.CheckFnctn() = 0 );
GO

Answer 4

, . CHECK FKs ( -) , , , :

create table dbo.Parents (
    ParentID int IDENTITY(1,1) not null,
    ValidFrom datetime not null,
    ValidTo datetime not null,
    /* Natural Key column(s) */
    CONSTRAINT PK_dbo_Parents PRIMARY KEY (ParentID),
    CONSTRAINT UQ_dbo_Parents_DRI UNIQUE (ParentID, ValidFrom, ValidTo),
    /* Unique constraint on Natural Key */
    CONSTRAINT CK_dbo_Parents_ValidDates CHECK (ValidFrom <= ValidTo) /* Semi-open interval */
)
go
alter table dbo.Parents add constraint DF_dbo_Parents_ValidFrom DEFAULT (CURRENT_TIMESTAMP) for ValidFrom
go
alter table dbo.Parents add constraint DF_dbo_Parents_ValidTo DEFAULT (CONVERT(datetime,'99991231')) for ValidTo
go
create table dbo._Children (
    ChildID int IDENTITY(1,1) not null, /* We'll need this in the update trigger */
    ParentID int not null,
    ChildDate datetime not null,
    _ParentValidFrom datetime not null,
    _ParentValidTo datetime not null,
    CONSTRAINT PK_dbo__Children PRIMARY KEY (ChildID),
    CONSTRAINT FK_dbo__Children_Parents FOREIGN KEY (ParentID,_ParentValidFrom,_ParentValidTo) REFERENCES dbo.Parents (ParentID,ValidFrom,ValidTo) ON UPDATE CASCADE,
    CONSTRAINT CK_dbo__Children_ValidDate CHECK (_ParentValidFrom <= ChildDate and ChildDate < _ParentValidTo) /* See, semi-open */
)
go
alter table dbo._Children add constraint DF_dbo__Children_ChildDate DEFAULT (CURRENT_TIMESTAMP) for ChildDate
go
create view dbo.Children (ChildID,ParentID,ChildDate)
with schemabinding
as
select ChildID,ParentID,ChildDate from dbo._Children
go
create trigger dbo.T_Children_I on dbo.Children instead of insert
as
begin
    set nocount on

    insert into dbo._Children (ParentID,ChildDate,_ParentValidFrom,_ParentValidTo)
    select i.ParentID,i.ChildDate,p.ValidFrom,p.ValidTo
    from
        inserted i
            inner join
        dbo.Parents p
            on
                i.ParentID = p.ParentID
end
go
create trigger dbo.T_Children_U on dbo.Children instead of update
as
begin
    set nocount on
    if UPDATE(ChildID)
    begin
        RAISERROR('Updates to ChildID are not allowed',16,1)
        return
    end

    update c
    set
        ParentID = i.ParentID,
        ChildDate = i.ChildDate,
        _ParentValidFrom = p.ValidFrom,
        _ParentValidTo = p.ValidTo
    from
        inserted i
            inner join
        dbo._Children c
            on
                i.ChildID = c.ChildID
            inner join
        dbo.Parents p
            on
                i.ParentID = p.ParentID
end
go
insert into dbo.Parents(ValidFrom,ValidTo)
select '20081201','20090101' union all
select '20090201','20090301'
/* (2 row(s) affected) */
go
insert into dbo.Children (ParentID,ChildDate)
select 1,'20081215'
/* (1 row(s) affected) */
go
insert into dbo.Children (ParentID,ChildDate)
select 1,'20090115'
/*
Msg 547, Level 16, State 0, Procedure T_Children_I, Line 6
The INSERT statement conflicted with the CHECK constraint "CK_dbo__Children_ValidDate". The conflict occurred in database "Play", table "dbo._Children".
The statement has been terminated.
*/
go
update dbo.Parents set ValidTo = '20090201' where ParentID = 1
/* (1 row(s) affected) */
go
insert into dbo.Children (ParentID,ChildDate)
select 1,'20090115'
/* (1 row(s) affected) */
go
update dbo.Parents set ValidTo = '20090101' where ParentID = 1
/*
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_dbo__Children_ValidDate". The conflict occurred in database "Play", table "dbo._Children".
The statement has been terminated.
*/
go
insert into dbo.Children (ParentID,ChildDate)
select 2,'20090215'
/* (1 row(s) affected) */
go
update dbo.Children set ChildDate = '20090115' where ParentID=2 and ChildDate = '20090215'
/*
Msg 547, Level 16, State 0, Procedure T_Children_U, Line 11
The UPDATE statement conflicted with the CHECK constraint "CK_dbo__Children_ValidDate". The conflict occurred in database "Play", table "dbo._Children".
The statement has been terminated.
*/
go
delete from dbo.Children
/* (3 row(s) affected) */
go
/* Clean up after testing */
drop view dbo.Children
drop table dbo._Children
drop table dbo.Parents
go

This is for SQL Server. Tested in 2005, but should work at least 2000 and 2008. The bonus here is that even if the trigger is disabled (for example, nested triggers are disabled), you cannot end up with the wrong data in the base tables