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?