ORA-02264: name already used by an existing constraint
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.
ERROR :ORA-02264: name already used by an existing constraint
Your create table statement has four foreign keys all called centralsupplies_fk
. That is not allowed: constraint names must be unique within a schema. You must give each one a different name.
It is usual practice to include the referenced table in the key name. So
create table centralsupplies
(
wardnum int constraint centralsupplies_ward_fk references ward,
itemnum int constraint centralsupplies_generalsupplies_fk references generalsupplies,
drugnum int constraint centralsupplies_pharmasupplies_fk references pharmasupplies,
quantity_required varchar(20),
staffname varchar(10) references staff(stname),
staffnum int constraint centralsupplies_staff_fk references staff,
regnum int unique,
dateord date,
daterec date
)
Also you have another foreign key constraint on STAFFNAME which you have not named. You do not need to name constraints, the system will generate a unique one for you, but it's generally a good idea to name them, not least because it is easier to diagnose relational integrity error messages with meaningfully named constraints.
However, in this case the correct solution is to drop the STAFFNAME column. You already have a foreign on the STAFF table, and you should join to that table whenever you need to display a value for STAFFNAME. Besides you do not have a unique constraint on staff.stname
column, so the foreign key statement will fail: foreign keys can only reference primary or unique keys.
ORA-00955 name is already used by an existing object
Perhaps there is an INDEX
associated with the PRIMARY KEY CONSTRAINT
, and it is also named as PK_B
.
You can check it as :
SELECT * FROM USER_INDEXES WHERE TABLE_NAME='<table_name>';
If that's true, then do :
ALTER INDEX "PK_B" RENAME TO "PK_XYZ";
Update : Regarding ALTER INDEX
statement, few important points as mentioned by Justin in the comments
Oracle implicitly creates an UNIQUE
index to support the PRIMARY KEY CONSTRAINT
. Since, the index is of the same name that of the primary key, and now that the primary key is being modified, it is better to drop and re-create the index again as per the definition of the old primary key.
My conclusion :
- The primary key constraint is enforced through a unique index.
- If Oracle already finds an index – unique or non-unique – it uses it
for the primary key. - If the index was initially created as non-unique, it will continue to
show as non-unique, however it will actually be a unique index.
A good demonstration and quite detailed on other aspects too, by Arup : Primary Keys Guarantee Uniqueness? Think Again.
Related Topics
SQL Query to Bring Last Letter in a String to First Letter Position
Lock Escalation - What's Happening Here
Sql: Select Maximum Value for Each Unique Key
How to Extract Year and Month from Date in Postgresql Without Using To_Char() Function
How to Return a Table from a Stored Procedure
Is There Startswith or Contains in T SQL with Variables
SQL Server: How to Check If Clr Is Enabled
Sp Taking 15 Minutes, But the Same Query When Executed Returns Results in 1-2 Minutes
Database Design for Recurring Events with Exceptions
Using Isnull or Select Coalesce in Linq..
Join Four Tables Involving Left Join Without Duplicates
Teradata, Reset When, Partition By, Order By
SQL 2005 Force Table Rename That Has Dependencies