Check constraint question

From: Ruben Schoenefeld <Ruben.Schoenefeld_at_uni-oldenburg.de>
Date: Wed, 23 Aug 2006 22:11:55 +0200
Message-ID: <Pine.LNX.4.64.0608232144430.21578@support.hrz.uni-oldenburg.de>

-----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----- Received on Wed Aug 23 2006 - 15:11:55 CDT