check constraint question

From: Karel van der Walt <karelvdwalt_at_webmail.co.za>
Date: 22 Dec 2003 11:30:46 -0800
Message-ID: <e743ac89.0312221130.1d3992df@posting.google.com>

The detail

create table ASSETCLASS
(

    ASSETCLASS           char(2)                        not null,
    DESCRIPTION          char(50),
    DEPRECIATIONMETHOD   char(35)                       not null,
    USEFULLIFE           smallint                       not null

);

INSERT
INTO AssetClass (AssetClass, Description,DepreciationMethod, UsefulLife )
VALUES ('CS', 'Computer Software', 'Straight Line', 2 );

create table ASSET
(

    ASSETID              char(6)                        not null,
    ASSETCLASS           char(2)                        not null,
    DESCRIPTION          char(50)                       not null,
    COST                 numeric(8,2)                   not null,
    DATEOFPURCHASE       date                           not null,
    RESIDUALVALUE        numeric(8,2)                   not null,

    check (Cost > ResidualValue)
);

INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase, ResidualValue)
VALUES ('CS0003', 'CS', 'MS Office 2000', 2400.00, '2001-11-01', 0.00);

And now the one with the multi-table check constraint

create table DEPRECIATION
(

    ASSETID              char(6)                        not null,
    DEPRECIATIONDATE     date                           not null,
    DEPRECIABLEAMOUNT    numeric(8,2)                   not null,

    check (NOT EXISTS (select b.assetID
    from depreciation as a, asset as b, assetClass as c     where a.AssetID = b.AssetID and
    b.assetClass = c.AssetClass and
    a.depreciationDate > dateadd(year, c.usefulLife, b.dateOfPurchase) ))
);

INSERT
INTO Depreciation (AssetId, DepreciationDate, DepreciableAmount) VALUES ('CS0003', '2004-02-28', 600.00); Allowed but subsequent inserts fail????? It seems as if my dbms evaluates the check on the existing table before inserting the new record???

Karel Received on Mon Dec 22 2003 - 13:30:46 CST