From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: SOLVED - foreign keys and lots of tables
Date: Thu, 19 May 2011 09:38:47 +0100
User-Agent: KMail/1.9.10
References: <201105182010.19753.gary.stainburn@ringways.co.uk>
	<20110519010907.770c8c48@karnak.local>
In-Reply-To: <20110519010907.770c8c48@karnak.local>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201105190938.47857.gary.stainburn@ringways.co.uk>

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