1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-02443 and ORA-02429.,

Discussion in 'SQL PL/SQL' started by Vicky, Jun 2, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    while dropping a constraint,

    >ALTER TABLE employees DROP CONSTRAINT "PAYMENT_PK"

    I faced the error below:

    Error report:
    SQL Error: ORA-02443: Cannot drop constraint - nonexistent constraint
    02443. 00000 - "Cannot drop constraint - nonexistent constraint"
    *Cause: alter table drop constraint <constraint_name>
    *Action: make sure you supply correct constraint name.

    So, i tried dropping the index in the same name,

    >DROP index "PAYMENT_PK"

    Error report:
    SQL Error: ORA-02429: cannot drop index used for enforcement of unique/primary key
    02429. 00000 - "cannot drop index used for enforcement of unique/primary key"
    *Cause: user attempted to drop an index that is being used as the
    enforcement mechanism for unique or primary key.
    *Action: drop the constraint instead of the index.

    while dropping constraint, it shows no constraint exist in the name, but while dropping the index in the same name it shows "cannot drop index used for enforcement of unique/primary key". Can any one tell me how to drop the constraint and create a new one.,?!
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    can you provide desc employees and


    SELECT * FROM all_constraints
    where constraint_name='PAYMENT_PK'
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    The Drop constraints works fine - see script below:

    Code (SQL):
    SQL> CREATE TABLE employees
      (
        empno   NUMBER(6),
        name    VARCHAR2(30),
        dept_no NUMBER(2),
        CONSTRAINT emp_pk PRIMARY KEY(empno) USING INDEX
        (CREATE INDEX emp_pk_idx ON employees
          (empno
          )
        )
      );  2    3    4    5    6    7    8    9   10   11

    TABLE created.

    SQL> ALTER TABLE employees DROP CONSTRAINT emp_pk;

    TABLE altered.
    As requested by Jagadekara above, please provide some more details on how you created the table employees (SQL for creation) + SQL for constraint and indexes (if not included in same script).

    Thanks.
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    May be he don't have that constraint, I mean name is different.

    Press F4 on table name in toad and click on constraints, there you can see your exact constraint name.

    then alter table table name drop constraint constraint_name will work.
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Yes, I also think so. There is probably no such primary key on that table.

    If you follow a minimum of nomenclature standards, you won't be having a Primary key called "PAYMENT_PK" on a table named "EMPLOYEES"; you would rather name it "EMPLOYEE_PK". And by the error message you're getting, there is indeed such a primary key + index but most probably linked to another table not EMPLOYEES.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Obviously you're wrong as Oracle clearly states such a constraint type DOES exist and as an example:


    Code (SQL):

    SQL> CREATE TABLE payment(
      2          pmtid   NUMBER,
      3          pmtdt   DATE,
      4          amt     NUMBER,
      5          overdue varchar2(1),
      6          procdt  DATE);


    TABLE created.


    SQL>
    SQL> CREATE UNIQUE INDEX payment_pk ON payment(pmtid);


    INDEX created.


    SQL>
    SQL> ALTER TABLE payment ADD CONSTRAINT pmt_pk PRIMARY KEY(pmtid) USING INDEX payment_pk;


    TABLE altered.


    SQL>
    SQL> ALTER TABLE payment DROP CONSTRAINT payment_pk;
    ALTER TABLE payment DROP CONSTRAINT payment_pk
                                        *
    ERROR at line 1:
    ORA-02443: Cannot DROP CONSTRAINT  - nonexistent CONSTRAINT

    SQL>
    SQL> DROP INDEX payment_pk;
    DROP INDEX payment_pk
               *
    ERROR at line 1:
    ORA-02429: cannot DROP INDEX used FOR enforcement OF UNIQUE/PRIMARY KEY

    SQL>

    SQL> ALTER TABLE payment DROP CONSTRAINT pmt_pk;


    TABLE altered.


    SQL>
    SQL> DROP INDEX payment_pk;


    INDEX dropped.


    SQL>
     

    It is possible to name a constraint differently from the index that supports it.
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx for every one,. But, here the problem, first I dropped table with cascading constraints and while I try to create a new table and constraint with the same name, the table got created but not the constraint, it shows,

    SQL Error: ORA-02264: name already used by an existing constraint
    02264. 00000 - "name already used by an existing constraint"
    *Cause: The specified constraint name has to be unique.
    *Action: Specify a unique constraint name for the constraint.

    And while I tried to delete it, it shows,

    Error report:
    SQL Error: ORA-02443: Cannot drop constraint - nonexistent constraint
    02443. 00000 - "Cannot drop constraint - nonexistent constraint"
    *Cause: alter table drop constraint <constraint_name>
    *Action: make sure you supply correct constraint name.

    I read that the index in the same name should be deleted before creating the constraint in the same name, but while deleting the index it shows,

    SQL Error: ORA-02429: cannot drop index used for enforcement of unique/primary key
    02429. 00000 - "cannot drop index used for enforcement of unique/primary key"
    *Cause: user attempted to drop an index that is being used as the
    enforcement mechanism for unique or primary key.
    *Action: drop the constraint instead of the index.

    And Now how can I created the constraint in the same name., And, Yes Rajen the table name was payment previously.,
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    When you dropped the table PAYMENT with CASCADE constraints, it drops the constraints also.

    Could you post the result of the following query please:

    Code (SQL):
    SELECT * FROM all_constraints
    WHERE constraint_name='PAYMENT_PK'
    Thanks.
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    From yesterday we are asking him to provide data for

    SELECT * FROM all_constraints
    WHERE constraint_name='PAYMENT_PK'

    I don't know why he is not providing adequate info..
     
  10. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I apologize for the delay,

    here it is.,

    SELECT * FROM all_constraints
    where constraint_name='PAYMENT_PK'


    OWNER cONSTRAINT_NAME cONSTRAINT_TYPE TABLE_NAME STATUS DEFERRTABLE DEFERRED VALIDATED GENERATED LAST_CHANGE INDEX_NAME

    TEST_RUN PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 17-MAY-12 PAYMENT_PK
    DEMO PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 30-JUL-09 PAYMENT_PK
    LIVE_DEMO PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 20-JUL-12 PAYMENT_PK
    INTERMOB PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 08-NOV-11 PAYMENT_PK
    IOCL PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 26-NOV-10 PAYMENT_PK
    GENERIC PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 30-NOV-11 PAYMENT_PK
    HOST PAYMENT_PK P USER_NAME ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 23-JUL-12 PAYMENT_PK
    NEFT PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 17-AUG-12 PAYMENT_PK
    LIVE_RUN PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 21-SEP-12 PAYMENT_PK
    DEMO_RUN PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 29-OCT-12 PAYMENT_PK
    INTERIOCL PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 08-FEB-13 PAYMENT_PK
    HOST_DEMO PAYMENT_PK P PAYMENT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 20-FEB-13 PAYMENT_PK
     
  11. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    The table name is not EMPLOYEES but PAYMENT; therefore the instruction to drop the constraint would be:

    Code (SQL):
    ALTER TABLE PAYMENT DROP CONSTRAINT PAYMENT_PK;

    (This also means that you haven't dropped the PAYMENT table).
     
  12. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi, Rajen. I'm using the Host Schema now., And it shows in the Host Schema only the USER_NAME table has the constraint PAYMENT_PK but not PAYMENT table.,
     
  13. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    1 !doubt, should the constraint name needs to be unique for the entire schema?!
     
  14. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    No it's unique for a given schema.
    The table name is "USER_NAME" (although I find such a table name quite strange ... most probably a typing error during creation of Primary key ;)) , so use:

    Code (SQL):
    ALTER TABLE USER_NAME DROP CONSTRAINT PAYMENT_PK;
     
    Vicky likes this.
  15. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thnx Rajen., I'll drop it and try creating a new 1 in the same name., So, the constraint name should be unique for the entire schema, right?! Bcz I thought that it's enough to be unique for the table alone.,
     
  16. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Yes, constraint name should be unique within a schema.
    But you can use same name for index (like PAYMENT_PK for Primary Key and Index Name)
     
    Vicky likes this.