Thread: constraint question (I think)

Hi folks.

I'm trying to model  a vehicle compound structure and have the following:

Four seperate compounds, North, East, South and West,
Each compound has a number of rows with Labels fastened to the fences
As the compounds are not perfect rectangles, the rows are of differing 
lengths.

I have tables for the compounds and rows, but can't work out how to set the 
constraints on the bays table - a seperate table while development takes 
place, merged with our existing stock table eventually;

I've managed to add the constraint to prevent compound_bay records being 
created for compound rows that do not exist,

My question is, how do I prevent a record being created for a bay higher than 
than the length of the row, i.e. > 20 in North row A or > 40 in East row A?

-- list of available compounds
create table compounds ( co_id        int4 primary key, co_name    varchar(80)
);

-- description of rows per compound
create table compound_rows ( co_id        int4 references compounds(co_id), cr_id        int4 not null, cr_label
varchar(5)not null, cr_sequence    int4 not null, cr_length    int4, primary key    (co_id, cr_id)
 
);
create unique index "compound_rows_sequence_index"  on compound_rows using btree (co_id, cr_sequence);
create unique index "compound_rows_label_index"  on compound_rows using btree (co_id, cr_label);

COPY compounds (co_id,co_name) from stdin;
1    North
2    East
3    South
4    West
\.

COPY compound_rows (co_id,cr_id,cr_label,cr_sequence,cr_length) from stdin;
1    1    A    10    20
1    2    B    20    20
1    3    C    30    25
1    4    D    40    25
2    1    A    10    40
2    2    B    20    40
2    3    C    30    45
2    4    D    40    45
\.

-- link from stock table one bay per vehicle
create table compound_bays ( st_VIN    varchar(17) primary key references stock (st_VIN), co_id        int4 not null,
cr_id       int4 not null, cb_id        int4 not null
 
);

alter table compound_bays add constraint compound_bays_row_check foreign key (co_id, cr_id) references
compound_rows(co_id,cr_id);


-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

I have managed to develop one solution using functions and triggers. Has 
anyone got a better solution?

Gary

create unique index "compound_bays_unique_index" on compound_bays using btree 
(co_id,cr_id,cb_id);

create or replace function compound_rows_range_check() returns trigger as 
$proc$
DECLARE  BAYNO int4;
BEGIN -- if changing compound or row fail IF NEW.co_id <> OLD.co_id THEN   RAISE EXCEPTION 'cannot change compound id';
ENDIF; IF NEW.cr_id <> OLD.cr_id THEN   RAISE EXCEPTION 'cannot change row id'; END IF; SELECT cb_id into BAYNO from
compound_bayswhere     co_id = NEW.co_id and     cr_id = NEW.cr_id and     cb_id > NEW.cr_length     order by cb_id
desc    limit 1; IF found THEN   RAISE EXCEPTION 'Cannot remove occupied bays: % > %',       BAYNO, NEW.cr_length; END
IF;RETURN NEW;
 
END;
$proc$ LANGUAGE plpgsql;

CREATE TRIGGER compound_rows_range_check BEFORE UPDATE on compound_rows FOR EACH ROW EXECUTE PROCEDURE
compound_rows_range_check();

create or replace function compound_bays_range_check() returns trigger as 
$proc$
DECLARE  ROWLENGTH int4;
BEGIN SELECT cr_length into ROWLENGTH from compound_rows where     co_id = NEW.co_id and     cr_id = NEW.cr_id; IF not
foundTHEN   RAISE EXCEPTION 'Compound / Row not found';  END IF; IF NEW.cb_id > ROWLENGTH THEN   RAISE EXCEPTION 'row
lengthexceeded: % > %',     NEW.cb_id,ROWLENGTH; END IF; RETURN NEW;
 
END;
$proc$ LANGUAGE plpgsql;

CREATE TRIGGER compound_bays_range_check BEFORE INSERT OR UPDATE on 
compound_bays FOR EACH ROW EXECUTE PROCEDURE compound_bays_range_check();



-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

I would develop like this (No so many changes, it is basically a small trigger)