Re: check constraint question - still perplexed

>> ok I think I now understand how to relate the record/fields [sic]
to be inserted with existing data in the dC. <<

I think that you are missing some constraints and qualifiers that you want to have:

CREATE TABLE Asset_classes
(asset_class CHAR(2) NOT NULL,
 description CHAR(50) DEFAULT '{none]' NOT NULL,  depreciation_method CHAR(35) NOT NULL, -- no codes?  useful_life INTEGER NOT NULL);

This was fine.

CREATE TABLE Assets
(asset_id CHAR(6) NOT NULL,
 asset_class CHAR(2) NOT NULL

    REFERENCES Asset_classes (asset_class)     ON UPDATE CASCADE,
 description CHAR(50) DEFAULT '{none]' NOT NULL,  cost NUMERIC(8,2) NOT NULL,
 date_of_purchase DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,  residual_value NUMERIC(8,2) NOT NULL,
 CHECK (cost > residual_value));

Need to have a Foreign Key here.

CREATE TABLE Depreciation
(asset_id CHAR(6) NOT NULL,
 depreciation_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,  depreciable_amount NUMERIC(8,2) NOT NULL,  CHECK (NOT EXISTS

       (SELECT *
         FROM Assets AS A, Asset_classes AS C
        WHERE Depreciation.asset_id = A.asset_id 
          AND A.asset_class = C.asset_class 
          AND Depreciation.depreciation_date >= DATEADD(YEAR,

The SELECT in the CHECK() constraint will go to the FROM clause do to scoping rules and not to the Depreciation table for the column names. Received on Wed Jan 07 2004 - 22:25:24 CET