Re: create table error with constraint...
Date: Fri, 25 Jul 2008 12:17:41 -0700 (PDT)
On Jul 25, 11:13 am, t..._at_panix.com (Dan Blum) wrote:
> Volker Hetzer <firstname.lastn..._at_ieee.org> wrote:
> > Hi!
> > I've got a simple problem with a create table statement:
> > CREATE TABLE HB_ATTRIBUTE (
> > STRINGVALUE VARCHAR2(4000 CHAR),
> > DATEVALUE DATE,
> > CONSTRAINT TCC_HB_ATTRIBUTE_1 CHECK ((STRINGVALUE is null)<>(DATEVALUE is
> > null)) DEFERRABLE INITIALLY DEFERRED);
> > This gives me an "ORA-00907: missing right paranthesis" at the "<>".
> > I have no idea what I'm doing wrong.
> > I'm using 10.2.0.1.0 on linux 32 bit.
> > Can anybody help me out?
> You can't compare boolean values that way in SQL. You can do something like this:
> ((decode(stringvalue, NULL, 1, 0) != decode(datevalue, NULL, 1, 0))
> Dan Blum t..._at_panix.com
> "I wouldn't have believed it myself if I hadn't just made it up."
Dan is right. Under the ANSI/ISO standards NULLs are never equal to anything.
You could also use the "is null" clause to define your check constraint if think this format is easier to read.
UT1 > get t8
1 alter table marktest
2* add constraint marktest_ck check (fld1 is not null OR fld2 is not null)
UT1 > /
HTH -- Mark D Powell -- Received on Fri Jul 25 2008 - 14:17:41 CDT