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.

The databases have differences, no way around it. One thing I noticed is both of them keep going after errors, so just put the 'same' code twice, one for each ;) — okaram
– okaram, Commented May 28, 2014 at 14:35
The databases still have differences, the idea is let them equal, with time. I didn't understand your point man, sorry. At top of the script I've put the ´BEGIN´ and at end I've put the 'COMMIT'. So, if I have any error, it won't execute nothing — Fabricio Mariani
– Fabricio Mariani, Commented May 28, 2014 at 14:46
All Postgres 8.x versions are end-of-life (8.4 will only be supported for 2 more months from now on). So there is absolutely no reason to use Postgres 8.x for anything new — user330315
– user330315, Commented May 28, 2014 at 19:23
I normally did scripts for creating the db structure, so there's no need for committing, and this allowed me to cheat; I didn't have any commits at all. — okaram
– okaram, Commented May 29, 2014 at 0:57
You could use a macro processor and generate 2 different scripts, one for postgresql and one for oracle from the same source ... every C compiler comes with one, or you could use something like m4 ... not sure I'd recommend it; hopefully somebody has a better idea — okaram
– okaram, Commented May 29, 2014 at 0:58

Tony Andrews · Accepted Answer · 2014-05-28 16:40:54Z

7

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');

edited May 28, 2014 at 16:40

answered May 28, 2014 at 14:36

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fabricio Mariani Over a year ago

Thank you for your prompt answer man. Let me check if I got it: I have a lot of constraints on my script, so I have to declare the exception whenever I drop one constraint? Another doubt, can I do this on postgres? Thank you!

Tony Andrews Over a year ago

If you create the procedure then you can call it many times over without declaring the exception. This is for Oracle, I don't know Postgres at all.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related