Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Check constraint question

Re: Check constraint question

From: Ruben Schoenefeld <Ruben.Schoenefeld_at_uni-oldenburg.de>
Date: Thu, 24 Aug 2006 23:08:09 +0200
Message-ID: <Pine.LNX.4.64.0608242306540.1911@support.hrz.uni-oldenburg.de>


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Today Ed Prochak wrote in group comp.databases.oracle.misc:

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

Hi Ed -

not a silly question. The point of the post is that I wanted to know if it's going to work before I even give it to the DBA in charge of creating the database.

Ruben

iD8DBQFE7hVBKxQwayh8oDERAnDpAKCvQoBaKdSkFIlcjMBpZfbTOJryTwCglxkc Wvs4G2PBmcE5AQ0Whbni8Mk=
=K7kr
-----END PGP SIGNATURE----- Received on Thu Aug 24 2006 - 16:08:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US