Oracle unique constraint and primary key not null
Solution 1
Q: Can a table have a primary key attribute and a unique constraint to another attribute?
A: Yes:
A table can have no more than one primary key.
A primary key may consist of multiple columns (a "composite primary key")
Any column may have a "unique constraint", whether or not it's a primary key column
A primary key is always "unique", and always has a "unique" constraint
ERROR at line 1: ORA-02261: such unique or primary key already exists in the table
A: Check your schema. You've already already got a primary key, and/or you already defined the same unique constraint.
For example:
http://www.shutdownabort.com/dbaqueries/Structure_Constraints.php
col type format a10
col cons_name format a30
select decode(constraint_type,
'C', 'Check',
'O', 'R/O View',
'P', 'Primary',
'R', 'Foreign',
'U', 'Unique',
'V', 'Check view') type
, constraint_name cons_name
, status
, last_change
from dba_constraints
where table_name like 'BRANCH'
order by 1
Solution 2
You can have a unique contraint apart from the primary key, but the message indicates that you already added such a constraint.
Comments
-
David Garcia almost 2 years
Can a table have a primary key attribute and a unique constraint to another attribute?
I have the following
CREATE TABLE BRANCH( BRA_CODE NUMBER NOT NULL PRIMARY KEY, BRA_NAME VARCHAR(15), BRA_ADDR VARCHAR(30), CITY_ID NUMBER);
and im trying to add
ALTER TABLE BRANCH ADD CONSTRAINT UNIQUE_BRANCH_NAME UNIQUE (BRA_NAME);
and i get the following error;
ERROR at line 1: ORA-02261: such unique or primary key already exists in the table