Re: Check constraint question

On Thu, 24 Aug 2006 23:08:09 +0200, Ruben Schoenefeld <Ruben.Schoenefeld_at_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