Thread: foreign keys and lots of tables
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
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) *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
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