Check Constraint pomoć?

[ plur @ 10.05.2008. 09:37 ] @

Pozdrav,

Imam sljedecu tablicu (Oracle):

Code:

CREATE TABLE PROBA
(
  ID       NUMBER(10),
  IME      VARCHAR2(20),
  PREZIME  VARCHAR2(20),
  TEST_ID  NUMBER(10)
);

Zanima me kako napraviti CONSTRAINT za kolonu TEST_ID,
koja mora biti unique (jedinstvena) ako nije NULL (ovako nekako):

IF (TEST_ID <> NULL)
THEN TEST_ID mora biti UNIQUE

Mozda nesto slicno ovim check constraint-ima:

Code:
ALTER TABLE PROBA ADD
CONSTRAINT TEST_CHK
CHECK (CASE WHEN test_id<>NULL THEN test_id_mora_biti_unique ; END CASE;)
ENABLE
VALIDATE;

Code:
ALTER TABLE proba ADD
CONSTRAINT test_chk
CHECK (CASE WHEN
    (SELECT COUNT (test_id)
        FROM proba
        WHERE test_id <> NULL
        HAVING COUNT (test_id) >= 1
    )<>0
  THEN  TRUE   END CASE)
ENABLE
VALIDATE;

HVALAAAAA!