CODEWITHSUNDEEP
sqloracle-databasealter
Abdul qadeer
Abdul qadeer

Reputation: 1

Cannot drop constraint - nonexistent constraint

I am trying to drop constraints in my table like fk and pk but its raising an error.

Cannot drop constraint - nonexistent constraint

But when I check whether there is a constraint or not, it shows that there is a constraint, but still the drop isn't working.

select column_name,constraint_name from user_cons_columns where table_name='EMP';

COLUMN_NAME CONSTRAINT_NAME
------------------------------
EMPNO       PK_EMP
DEPTNO      FK_DEPTNO


alter table emp drop constraint deptno;

ERROR at line 1: ORA-02443: Cannot drop constraint - nonexistent constraint

Upvotes: 0

Views: 13665

Answers (4)

rifat khandker
rifat khandker

Reputation: 11

It happens When you try to drop a constraint in child table but it actually exist in parent table. that's why you are showed nonexistent constraint. a query that bring you to the desired table is like:

select TABLE_NAME from dba_cons_columns where CONSTRAINT_NAME='constraint_name';

# result
TABLE_NAME
------------------------------
table_name

now you disable/drop that constraint from the resulted table.

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
# Table altered.

ALTER TABLE table_name DROP CONSTRAINT constraint_name;
# Table altered.

Upvotes: 0

AABHINAAV
AABHINAAV

Reputation: 33

alter table emp drop constraint fk_deptno;

Upvotes: 1

Jay Desai
Jay Desai

Reputation: 861

Try this:

ALTER TABLE EMP DROP CONSTRAINT FK_DEPTNO;

Upvotes: 1

Robert Hollon
Robert Hollon

Reputation: 76

The syntax requires the table with the foreign key and the name of the constraint. After running into the same issue multiple times I try to call both to make sure I am not accidentally calling the wrong table.

alter [NameOfTable] Drop Constraint [NameOfConstraint]

Upvotes: 0

Related Questions