Re: check constraint question - still perplexed

From: Karel van der Walt <karelvdwalt_at_webmail.co.za>
Date: 8 Jan 2004 12:23:44 -0800
Message-ID: <e743ac89.0401081223.6a9a1437_at_posting.google.com>

Solved

Qualifying the ambiguous column in the check constraint fixes things. Thus

> 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 Depreciation.AssetID = a.AssetID and
> a.assetClass = b.AssetClass and
> depreciationDate >= dateadd(year, b.usefulLife, a.dateOfPurchase) ))
> );

Considering the biz Rule
Accumulated depreciation of an asset must be less than or equal to diffrence between cost and residual value of that asset yields
 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

  depreciation as a,asset as b where
  a.assetID = Depreciation.AssetID and a.assetID = b.AssetID   group by a.assetID,b.ResidualValue,b.cost having   Sum(a.DepreciableAmount)+Depreciation.DepreciableAmount > b.cost-b.ResidualValue)
)
 );
which to me suggests that even though the record/fields to be inserted must be qualified they are not physically added to the table and then removed when the assert on the table fails. Received on Thu Jan 08 2004 - 21:23:44 CET