-----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)

iD8DBQFE7LaSKxQwayh8oDERAtARAJ9ufrX4ywXAU0Wa7VmJgc eZiHdRcwCgsol/
kKwipKubvcLYa+CC4lHAzrE=
=fo9U
-----END PGP SIGNATURE-----