Multiple Primary Keys

Posted on 2005-04-11
Last Modified: 2008-02-01
I get an oracle 9i error saying it a table can't have more than one primary key.  Isn't there such a thing as a composite key?
Question by:lcor
    LVL 25

    Assisted Solution


    CREATE TABLE track(
      album CHAR(10) NOT NULL,
      posn INTEGER NOT NULL,
      song VARCHAR(255),
      PRIMARY KEY (album, dsk, posn)
    LVL 11

    Accepted Solution

    You can't have more than one primary key in a table. But you can have one primary key defined over multiple columns. See the example given above.
    LVL 47

    Assisted Solution

    But you can define UNIQUE keys on different columns. The disadnatage is that Oracle automatically creates indexes on PRIMARY and UNIQUE key constraints and this slws down DML operation on the table.
    LVL 7

    Assisted Solution

    by:Mehul Shah
    The problem is with the way you define the constraints during the table creation. Some are column level contraints which follows the column defination

    emp_id number(4) primary key
    SSN varchar2(20) unique

    You can also have table level contraints. When you are defining a constraint on multiple columns or you are checking the value of column2 based on column1 then you need to define it at a table level.

    So if you see the example mentioned by jrb1. We have defined a primary key constraints on multiple columns(Composite Key) but its defined as a table level constraint.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now