ADD CONSTRAINT IF EXISTS (Oracle 11g, Postgres 8)

Question

I'm having difficult to make one script to delete the old constraints from some databases and after, create new ones with new references.

The problem is that the databases are not equal.

eg.: the swpmnh database has the fk_cmp_solicitaca_rh_contrat constraint but the swpmcs database has not. So if I execute the script I would have an error and it won't commit.

I know that Postgres 9.x has the possibility to do DROP CONSTRAINT IF EXISTS, but neither Postgres 8.x nor Oracle 11g have this function.

I'm working and studying SQL about only 3 months, I know that this is a simple thing, but it's being a problem for me.

Answer 1

This is the error you will be getting:

SQL> alter table my_tab drop constraint my_cons;
alter table my_tab drop constraint my_cons
                                   *
ERROR at line 1:
ORA-02443: Cannot drop constraint  - nonexistent constraint

You can trap the ORA-02443 error in PL/SQL and ignore it (using dynamic SQL):

  1  declare
  2     e exception;
  3     pragma exception_init (e, -2443);
  4  begin
  5     execute immediate 'alter table my_tab drop constraint my_cons';
  6  exception
  7     when e then null;
  8* end;
SQL> /

PL/SQL procedure successfully completed.

That is a bit verbose, so you could create a handy procedure:

create or replace procedure drop_constraint (p_table varchar2, p_constraint varchar2) is
   e exception;
   pragma exception_init (e, -2443);
begin
   execute immediate 'alter table ' || p_table || ' drop constraint '||p_constraint;
exception
   when e then null;
end;

Then use it whenever you need it:

execute drop_constraint ('my_tab', 'my_cons1');
execute drop_constraint ('my_tab', 'my_cons2');
execute drop_constraint ('another_tab', 'another_cons');