Primary key using output of function rather than column

[cjard]

I see that we can create indexes on function output instead of column value, and I wonder if we can also do that with PKs..

Basically I have a 2 part PK.. One is based on a number, one is based on whether another column is null or not (only)

So lets say for this data:

1, A
1, null
2, A
2, null

is allowed but this is NOT:

1, A
1, B


nor is this:

1, A
1, null
1, null


How do I make a PK so that it enforces the rule that:

"The combination of col1 and NVL2(col2, 0, 1) is unique"


I've tried:

ALTER TABLE myTable ADD (  CONSTRAINT myPK UNIQUE (theNumber, NVL2(theLetter, 0, 1))  ENABLE  VALIDATE  );

But it doesnt seem to work

[cjard]

note; i have succeeded in creating a unique index for this, using a function.. i dont really see why a function cannot be used in a primary key - if there's a good explanation out there, i'd love to see it

[hrishy]

Hi

This is what i can think of in 9i and above

CREATE TABLE myTable
(
  theNumber NUMBER(6),
  TheLetter char(1)
  CONSTRAINT theNumber_pk primary key(theNumber)
    USING INDEX
    (CREATE UNIQUE INDEX my_pk_idx 
	ON employees(theNumber, NVL2(theLetter, 0, 1))
);

i dont think so you can create a primary key on a column that is non existant like theNumber, NVL2(theLetter, 0, 1)

But 11g i know introduces the concept of Virtual columns but again i am not sure if virtual columns can be part of primary keys.

regards
Hrishy