Thanks Dave, I came up with the same answer after much googling. I managed to write a trigger to do this and it worked first time :D
Then I realised I was going to have to write more triggers to handle the reverse side, i.e. deleting a coach_train record after a booking has been made. Thanks anyway. Gary On Thursday 19 May 2011 01:09:07 David W Noon wrote: > 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. -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
