CODEWITHSUNDEEP
oracle-databaseoracle11g
RNJ
RNJ

Reputation: 15552

Drop auto generated constraint name

I have an auto-generated primary key constriant. The key is actually wrong. It should be against two columns and currently it is against one. I need to run this script on multiple databases and so the constraint name may be different for each database. I need to get the constraint name and then drop it and then create the new primary key.

here is the script I have so far

DECLARE
con_name VARCHAR(255);

BEGIN

select constraint_name into con_name from all_constraints where table_name = 'MY_TABLE' and constraint_type = 'P';

EXECUTE immediate 'ALTER TABLE MY_TABLE drop constraint con_name';

EXECUTE immediate 'ALTER TABLE MY_TABLE ADD CONSTRAINT MT_PK PRIMARY KEY (REV, ID)';

COMMIT;
/
EXIT;

However I have a problem in that I think it is trying to drop con_name rather than the value within that variable.

Error on script: Error number: -2443, Error Message: ORA-02443: Cannot drop constraint  - nonexistent constraint

Can anyone help? I basically need to drop the pk in this table. Is there any other way or is my approach the right sort of way.

Thanks

Upvotes: 0

Views: 3800

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Your con_name variable is out of scope within the DDL statement you're executing; you're trying to drop a constraint called con_name, not one named with the value that holds - as you suspected. You can't use a bind variable here so you'll need to concatenate the name:

DECLARE
  con_name all_constraints.constraint_name%type;
BEGIN
  select constraint_name into con_name
  from all_constraints
  where table_name = 'MY_TABLE' and constraint_type = 'P';

  EXECUTE immediate 'ALTER TABLE MY_TABLE drop constraint ' || con_name;

  EXECUTE immediate 'ALTER TABLE MY_TABLE ADD CONSTRAINT MT_PK PRIMARY KEY (REV, ID)';
END;
/

As Nicholas Krasnov pointed out in a comment, you don't need to do this at all; you can drop the primary key without specifying its name (also shown in the syntax diagram) without using dynamic SQL or a PL/SQL block:

ALTER TABLE MY_TABLE DROP PRIMARY KEY;
ALTER TABLE MY_TABLE ADD CONSTRAINT MT_PK PRIMARY KEY (REV, ID);

Hopefully you don't already have any tables with foreign key constraints against this PK.

Upvotes: 5

Related Questions