Wednesday, May 23, 2012

ORA-02260

This was tested on Oracle 11.2. I saw ORA-02260 for the first time today and decided to investigate further. If you want to ensure that more than 1 column holds unique values, you can do this by creating unique indexes or modifying the columns to be unique. However, if you try to explicitly set up more than one primary key on a table, you get this error:
 
SQL> create table andrews_table
  2  (col1 number,
  3   col2 number,
  4   col3 number,
  5   col4 number,
  6   col5 number,
  7   col6 number)
  8  /
 
Table created.
 
SQL> create unique index andrews_index1
  2  on andrews_table(col1)
  3  /
 
Index created.
 
SQL> create unique index andrews_index2
  2  on andrews_table(col2)
  3  /
 
Index created.
 
SQL> alter table andrews_table
  2  modify (col3 unique)
  3  /
 
Table altered.
 
SQL> alter table andrews_table
  2  modify (col4 unique)
  3  /
 
Table altered.
 
SQL> alter table andrews_table
  2  add constraint pk1 primary key(col5)
  3  /
 
Table altered.
 
SQL> alter table andrews_table
  2  add constraint pk2 primary key(col6)
  3  /
add constraint pk2 primary key(col6)
                   *
ERROR at line 2:
ORA-02260: table can have only one primary key
 
SQL>

No comments: