Re: check constraint question - still perplexed

From: Karel van der Walt <karelvdwalt_at_webmail.co.za>
Date: 6 Jan 2004 22:31:38 -0800
Message-ID: <e743ac89.0401062231.7ad7a01f_at_posting.google.com>

OK i think i now understand how to relate the record/fields to be inserted with existing data in the db.

So here goes again

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 );

INSERT
INTO AssetClass (AssetClass, Description,DepreciationMethod, UsefulLife ) VALUES ('MV', 'Motor Vehicles', 'Sum of Digits', 5 );

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);

INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase, ResidualValue)
VALUES ('MV0005', 'MV', 'LDV HGG702 GP', 30000.00, '1998-06-01', 7500.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 a.assetID
    from asset as a, assetClass as b
    where AssetID = a.AssetID and
    a.assetClass = b.AssetClass and
    depreciationDate >= dateadd(year, b.usefulLife, a.dateOfPurchase) )) );

INSERT
INTO Depreciation (AssetId, DepreciationDate, DepreciableAmount) VALUES ('CS0003', '2003-05-31', 600.00); works but

INSERT
INTO Depreciation (AssetId, DepreciationDate, DepreciableAmount) VALUES ('CS0003', '2003-06-07', 600.00); fails - the usefullLife of MV are used and not that of CS ????

select a.assetID

    from asset as a, assetClass as b
    where 'CS0003' = a.AssetID and
    a.assetClass = b.AssetClass and
    <put date here> = dateadd(year, b.usefulLife, a.dateOfPurchase)

only returns assetID for dates >= 2003-11-01 which is what one would expect, however when used as search condition in check constraint things go wrong.

If this is a genuine bug in my dbms??

Tx for your time Received on Wed Jan 07 2004 - 07:31:38 CET