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

      

+3


source to share


2 answers


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

      

+4


source


You may have a unique opposition other than the primary key, but the message indicates that you have already added such a constraint.



+4


source







All Articles