Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 868
  • Last Modified:

Multiple Primary Keys

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?
0
lcor
Asked:
lcor
4 Solutions
 
jrb1Commented:
Sure.  

CREATE TABLE track(
  album CHAR(10) NOT NULL,
  dsk INTEGER NOT NULL,
  posn INTEGER NOT NULL,
  song VARCHAR(255),
  PRIMARY KEY (album, dsk, posn)
)
0
 
sujit_kumarCommented:
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.
0
 
schwertnerCommented:
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.
0
 
Mehul ShahIT consultantCommented:
The problem is with the way you define the constraints during the table creation. Some are column level contraints which follows the column defination

eg
emp_id number(4) primary key
or
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now