DBAsupport.com Forums - Powered by vBulletin

View Poll Results: Expressin in Primary Field

Voters
2. You may not vote on this poll
  • Expression

    0 0%
  • Primary key

    2 100.00%
Results 1 to 2 of 2

Thread: Expression in Primary Key

  1. #1
    Join Date
    Mar 2001
    Posts
    36

    Question Expression in Primary Key

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width