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

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)
>
> iD8DBQFE7LaSKxQwayh8oDERAtARAJ9ufrX4ywXAU0Wa7VmJgc eZiHdRcwCgsol/
> kKwipKubvcLYa+CC4lHAzrE=
> =fo9U
> -----END PGP SIGNATURE-----

This sounds like a silly question, but it has to be asked:
Did you try it?

Ed

-----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)
>>
>> iD8DBQFE7LaSKxQwayh8oDERAtARAJ9ufrX4ywXAU0Wa7VmJgc eZiHdRcwCgsol/
>> 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

- --
Ruben Schoenefeld, ICQ 1-971-310, http://support.uni-oldenburg.de/~ruben/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFE7hVBKxQwayh8oDERAnDpAKCvQoBaKdSkFIlcjMBpZf bTOJryTwCglxkc
Wvs4G2PBmcE5AQ0Whbni8Mk=
=K7kr
-----END PGP SIGNATURE-----

On Thu, 24 Aug 2006 23:08:09 +0200, Ruben Schoenefeld
<Ruben.Schoenefeld@uni-oldenburg.de> wrote:

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

Ahh, I see, so you want US to try it.
The only thing I remember (from a while ago) is constructs like you
posted do NOT work, and you are limited to using DECODE expressions
returning *boolean* true or false.

--
Sybrand Bakker, Senior Oracle DBA

On Thu, 24 Aug 2006 23:08:09 +0200, Ruben Schoenefeld
<Ruben.Schoenefeld@uni-oldenburg.de> wrote:

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

There are freely downloadable versions of Oracle - it's generally cheaper to
get a computer to validate syntax than it is to ask a newsgroup, or to try and
code "blind" and post the results to someone else.

The full versions of Oracle are available for development, or there's Oracle
XE which is free even for production usage.

The short answers, though:

>>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.
....
>>, 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?

Yes, looks like it. I haven't tried it in an Oracle database either, but it
looks both straightforward and correct to me.

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

Curiously, no, Oracle haven't implemented an xor operator; an omission on
their part which you have worked around correctly.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

On Fri, 25 Aug 2006 01:24:52 +0200, Sybrand Bakker <postbus@sybrandb.demon.nl>
wrote:

>>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.
>
>Ahh, I see, so you want US to try it.

This remonstration is valid; the OP should try it first on his own database,
not ask other people to run what his own computer can run.

>The only thing I remember (from a while ago) is constructs like you
>posted do NOT work, and you are limited to using DECODE expressions
>returning *boolean* true or false.

The OP's expression does evauluate to Boolean true or false; it's a Boolean
expression. It WILL work.

A DECODE statement can NEVER return true or false, as there is no Boolean type
on the SQL level, only on the PL/SQL level. A DECODE will NOT work as the
subject of a check constraint - you'll just get "ORA-00920: invalid relational
operator"

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Andy Hassall wrote:
> On Thu, 24 Aug 2006 23:08:09 +0200, Ruben Schoenefeld
> <Ruben.Schoenefeld@uni-oldenburg.de> wrote:
>
[]

> >>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)
>
> Curiously, no, Oracle haven't implemented an xor operator; an omission on
> their part which you have worked around correctly.

Not an ommision by Oracle. There is no such operator in the SQL
standard.

It might be a nice enhancement though.

Ed

Ed Prochak wrote:
> Andy Hassall wrote:
>> On Thu, 24 Aug 2006 23:08:09 +0200, Ruben Schoenefeld
>> <Ruben.Schoenefeld@uni-oldenburg.de> wrote:
>>
> []
>
>>>> 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)
>> Curiously, no, Oracle haven't implemented an xor operator; an omission on
>> their part which you have worked around correctly.
>
> Not an ommision by Oracle. There is no such operator in the SQL
> standard.
>
> It might be a nice enhancement though.
>
> Ed

I'm not sure that is true though I'll readily acknowledge that I
have yet to test it.

$ cd $ORACLE_HOME/rdbms/admin
$ more stdspec.sql

There you will find, in the definition of the package STANDARD, the
following spec.

function XOR (LEFT BOOLEAN, RIGHT BOOLEAN) return BOOLEAN;
pragma BUILTIN('XOR',8, 3, 9); -- PEMS_INTEGER, INT_XOR
pragma FIPSFLAG('XOR', 1450);

Right between >= and NOT ... so I expect we have it but just
don't have it documented.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org