pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedforeign keys and lots of tables
2+ messages / 2 participants
[nested] [flat]
* foreign keys and lots of tables
@ 2011-05-18 19:10 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 2+ messages in thread
From: Gary Stainburn @ 2011-05-18 19:10 UTC (permalink / raw)
To: pgsql-sql
I have the following tables (individual seat allocation removed to make it
simpler)
create table coaches ( -- carriages
c_id serial primary key,
c_name varchar(20) not null
);
create table trains ( -- one for each train
t_id serial primary key
);
create table train_coaches ( -- which carriages are on what trains
t_id int4 not null references trains(t_id),
c_id int4 not null references coaches(c_id)
);
I now want to create bookings and allocate seats, but the seat must exist on
the coach_seats table *AND* only for a carriage included in the train, i.e.
an entry in train_coaches.
create table bookings (
b_id serial primary key,
t_id int4 not null references trains(t_id)
);
create table booking_seats (
b_id int4 not null references bookings(b_id),
c_id int4, -- carriage ID
c_seat varchar(10) -- seat label
);
The following ensures the seat exists on the coach. (not shown)
alter table booking_seats add constraint seat_exists
foreign key (c_id, c_seat) references coach_seats (c_id,c_seat);
How would I ensure that the coach exists on the train. I would need to convert
the b_id to a t_id using the bookings table and I don't know how.
To complicate things, when the initial booking is made, bot c_id and c_seat
are NULL. Will this make any difference?
Gary
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: foreign keys and lots of tables
@ 2011-05-19 00:09 David W Noon <dwnoon@ntlworld.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 2+ messages in thread
From: David W Noon @ 2011-05-19 00:09 UTC (permalink / raw)
To: pgsql-sql
On Wed, 18 May 2011 20:10:19 +0100, Gary Stainburn wrote about [SQL]
foreign keys and lots of tables:
>I have the following tables (individual seat allocation removed to
>make it simpler)
Omitting details makes the problem more difficult to comprehend.
[snip]
>create table booking_seats (
> b_id int4 not null references bookings(b_id),
> c_id int4, -- carriage ID
> c_seat varchar(10) -- seat label
>);
>
>The following ensures the seat exists on the coach. (not shown)
>
>alter table booking_seats add constraint seat_exists
> foreign key (c_id, c_seat) references coach_seats (c_id,c_seat);
>
>How would I ensure that the coach exists on the train. I would need to
>convert the b_id to a t_id using the bookings table and I don't know
>how.
I think you will need to write a trigger procedure with something like
the following query inside it:
IF NOT EXISTS(SELECT * FROM train_coaches AS tc
INNER JOIN bookings AS b ON b.t_id = tc.t_id
WHERE b.b_id = NEW.b_id AND tc.c_id = NEW.c_id)) THEN
-- Something is wrong.
You cannot use a subquery in a CHECK constraint, so I think a trigger
will be the go.
>To complicate things, when the initial booking is made, bot c_id and
>c_seat are NULL. Will this make any difference?
If c_id is NULL you cannot check anything against it, so your data
integrity has just gone for a Burton.
--
Regards,
Dave [RLU #314465]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
dwnoon@ntlworld.com (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
Attachments:
[application/pgp-signature] signature.asc (197B, ../../20110519010907.770c8c48@karnak.local/2-signature.asc)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-05-19 00:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-18 19:10 foreign keys and lots of tables Gary Stainburn <gary.stainburn@ringways.co.uk>
2011-05-19 00:09 ` David W Noon <dwnoon@ntlworld.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox