check constraint question
Date: 22 Dec 2003 11:30:46 -0800
Message-ID: <e743ac89.0312221130.1d3992df_at_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 - 20:30:46 CET