Date: Thu, 19 May 2011 01:09:07 +0100
From: David W Noon <dwnoon@ntlworld.com>
To: pgsql-sql@postgresql.org
Subject: Re: foreign keys and lots of tables
Message-ID: <20110519010907.770c8c48@karnak.local>
In-Reply-To: <201105182010.19753.gary.stainburn@ringways.co.uk>
References: <201105182010.19753.gary.stainburn@ringways.co.uk>
Organization: Luton Operatic Society
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=PGP-SHA1;
	boundary="Sig_/lk0mwarzN8MczEQte8+7rDx";
	protocol="application/pgp-signature"

--Sig_/lk0mwarzN8MczEQte8+7rDx
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

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 =3D tc.t_id
      WHERE b.b_id =3D NEW.b_id  AND tc.c_id =3D 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.
--=20
Regards,

Dave  [RLU #314465]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
dwnoon@ntlworld.com (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

--Sig_/lk0mwarzN8MczEQte8+7rDx
Content-Type: application/pgp-signature; name=signature.asc
Content-Disposition: attachment; filename=signature.asc

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.17 (GNU/Linux)

iEYEARECAAYFAk3UX60ACgkQc9/LpQ70v4+uRgCfdKuNKV5emFhUY6S/yETnqo9w
tZQAmwdpr+eZa5u4PVU1EvM1zRsvnmrh
=aPq2
-----END PGP SIGNATURE-----

--Sig_/lk0mwarzN8MczEQte8+7rDx--