Expression in Primary Key

[pinakpatel]

Hi,

I'm having problem in creation of primary key.

CREATE TABLE DB_SIZE
( INSTANCE_NAME VARCHAR2(8) NOT NULL,
DB_DATE DATE NOT NULL,
DATABASE_SIZE NUMBER(12,2) NOT NULL,
DB_ACTUALSIZE NUMBER(12,2) NOT NULL
)
/
Tables created.

ALTER TABLE DB_SIZE
ADD CONSTRAINT PR_INST_DBDATE PRIMARY KEY (INSTANCE_NAME,TO_CHAR(DB_DATE,'YYYYMMDD'))
/
PRIMARY KEY (INSTANCE_NAME,TO_CHAR(DB_DATE,'YYYYMMDD'))
*
ERROR at line 3:
ORA-00907: missing right parenthesis

First I can use expression in primary key or not..?
If yes What's wrong in SQL.

Thanks,
Pinakin

[slimdave]

No, you can't use an expression in a primary key.

What i think you are looking for here is a primary key defined on ...

(INSTANCE_NAME,DB_DATE)

...and a separate check constraint on db_date to say ...

db_date=trunc(db_date)