ORA-02264: name already used by an existing constraint [duplicate]

Question

I Am Working On SQL And I Am Facing A Problem In Creating The Table! Here Is My Code:

CREATE TABLE Voucher_Types
(
    ID Number(3, 0),
    Abbreviation VarChar(2),
    Voucher_Type VarChar(100),
    EntryBy VarChar(25),
    EntryDate Date,
    CONSTRAINT ID_PK Primary Key(ID)
);

And I Get The Following Error,
ORA-02264: name already used by an existing constraint
I Am Using Oracle10g
I Goggled It And Found Some Topics But They Didn't Help Me, Can Somebody Help Me In This Problem! Thanks In Advance..!

Answer 1

You have another table that has already a constrained with the name ID_PK.

If you want to find out which table it is, you might try

select owner, table_name from dba_constraints where constraint_name = 'ID_PK';

Most probably, you copied your create table statement but did not change the primary key's constraint name.

It is generally felt to be good practice to include the table name in a constraint's name. One reason is exactly to prevent such "errors".

So, in your case, you might use

CREATE TABLE Voucher_Types
(
   ... 
   CONSTRAINT Voucher_Types_PK Primary Key(ID)
);

Update Why can't the same constraint name be used twice? (As per your question in the comment): This is exactly because it is a name that identifies the constraint. If you have a violation of a constraint in a running system, you want to know which constraint it was, so you need a name. But if this name can refer multiple constraints, the name is of no particular use.

Answer 2

The error message tells you that there's already another constraint named ID_PK in your schema - just use another name, and you should be fine:

CREATE TABLE Voucher_Types
(
    ID Number(3, 0),
    Abbreviation VarChar(2),
    Voucher_Type VarChar(100),
    EntryBy VarChar(25),
    EntryDate Date,
    CONSTRAINT VOUCHER_TYPES_ID_PK Primary Key(ID)
);

To find the offending constraint:

SELECT * FROM user_constraints WHERE CONSTRAINT_NAME = 'ID_PK'

Answer 3

this means that there is a constraint named ID_PK try with CONSTRAINT Voucher_Types_ID_PK Primary Key(ID) for instance

you can check whether exists with

select * from user_constraints where upper(constraint_name) = 'ID_PK';

or

select * from all_constraints where upper(constraint_name) = 'ID_PK';