Re: Check constraint question

Ruben Schoenefeld wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi -
>
> I have a check constraint and I would like to know if it is going to
> run before I hand the script over to the people who will actually
> create the tables.
>
> For a signal database I have signal heads (the traffic lights) that
> are attached either to a mastarm (which is then attached to a signal
> pole) or to a structure (like an overpass, tunnel or so forth). I want
> to prevent that the signal head is attached to both.
>
> So I have a table called mastarm, with an ID as the primary key as
> well as a structure with an ID as the primary key.
> Table signalhead has an ID as the primary key and mastarm_id as well
> as structure_id as foreign keys.
>
> create table structure
> ( id number(10) not null
> , ...
> , constraint pk_structure primary key (id)
> );
>
> create table mastarm
> ( id number(10) not null
> , pole_id number(10) not null
> , ...
> , constraint pk_mastarm primary key (id)
> , constraint fk1_mastarm foreign key (pole_id)
> references pole (id)
> );
>
> create table signalhead
> ( id number(10) not null
> , structure_id number(10)
> , mastarm_id number(10)
> , ...
> , constraint pk_signalhead primary key (id)
> , constraint fk1_signalhead foreign key (structure_id)
> references structure (id)
> , constraint fk2_signalhead foreign key (mastarm_id)
> references mastarm (id)
> , constraint ck1_signalhead check
> (
> (mastarm_id is not null and structure_id is null)
> or
> (mastarm_id is null and structure_id is not null)
> )
> );
>
> Is the check constraint ck1_signalhead going to work and do what I
> want it to do?
>
> Instead of using an "or" and two "and"s, is there an "xor" that I can
> use? Like:
> constraint ck1_signalhead check (mastarm_id xor structure_id)
>
> Thanks,
> Ruben Schoenefeld
>
> - --
> Ruben Schoenefeld, ICQ 1-971-310, http://support.uni-oldenburg.de/~ruben/
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (GNU/Linux)
>
> iD8DBQFE7LaSKxQwayh8oDERAtARAJ9ufrX4ywXAU0Wa7VmJgceZiHdRcwCgsol/
> kKwipKubvcLYa+CC4lHAzrE=
> =fo9U
> -----END PGP SIGNATURE-----
This sounds like a silly question, but it has to be asked: Did you try it?

  Ed Received on Thu Aug 24 2006 - 14:48:13 CDT