0

I need to modify a table to remove the primary key constraint, the only problem is the table is generated by another system and I don't have the constraint name. So as a workaround I have created the following script which I thought should work but it's not, so can anyone please help me to write another script which will work.

ALTER TABLE temp 
MODIFY CONSTRAINT (select constraint_name 
                   FROM all_constraints 
                   WHERE owner like '%tempUser%' and 
                         table_name like '%temp%' and 
                         constraint_type = 'P'
                  ) 
DISABLE;

I am getting following error when trying to run.

SQL Error: ORA-14006: invalid partition name 14006. 00000 - "invalid partition name" *Cause: a partition name of the form is expected but not present. *Action: enter an appropriate partition name.

I tried following code based on suggested link and it run without any error but it does not remove the constraint.

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM all_constraints c
   where owner like '%tempUser%' and table_name like '%temp%' and constraint_type = 'P')
  LOOP
    dbms_utility.exec_ddl_statement('alter table ' || c.table_name || ' modify CONSTRAINT ' || c.constraint_name || ' disable ');
  END LOOP;
END;
/
Nakul Detroja
  • 61
  • 1
  • 6
  • What isn't working from this script? Is it giving an error? This will help to find the answer. – Steven Jun 21 '16 at 15:05
  • 1
    Possible duplicate of [Disable all table constraints in Oracle](http://stackoverflow.com/questions/128623/disable-all-table-constraints-in-oracle) – mustaccio Jun 21 '16 at 15:21

1 Answers1

1

You could use dynamic SQL to solve this. First find the name of your constraint and then create dynamic code to disable it.

Have a look at Disable all table constraints in Oracle, which is similar

Community
  • 1
  • 1
Jonny
  • 1,037
  • 7
  • 15