constraint question (I think)

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: constraint question (I think)
Date: 2008-12-04 10:01:46
Message-ID: 200812041001.46512.gary.stainburn@ringways.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Achilleas Mantzios 2008-12-04 15:02:18 array index access outside range
Previous Message Milan Oparnica 2008-12-02 23:10:59 Re: JOIN results of refcursor functions