Oracle unique constraint and primary key are not null
Can a table have a primary key attribute and a unique constraint on 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 am getting the following error:
ERROR at line 1:
ORA-02261: such unique or primary key already exists in the table
source to share
Q: Can a table have a primary key attribute and a unique constraint on another attribute?
A: Yes:
-
A table can have at most one primary key.
-
The primary key can have multiple columns ("composite primary key")
-
Any column can have a "unique constraint" regardless of whether it is a primary key column
-
The primary key is always "unique" and always has a "unique" constraint
ERROR on line 1: ORA-02261: such a unique or primary key already exists in the table
A: Check your circuit. You already have a primary key and / or you have 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
source to share