check before adding Constraint in table (oracle)

Question

i want to add Constraint in table but before adding i have to check is that Constraint existing in table or not like

IF NOT EXISTS(some condition)

ADD CONSTRAINT CHK_DATES_VALID
CHECK ((DATE_NORMAL != 'n' AND DATE_NORMAL != 'N') OR
       (DATE_SCHEDULED != 'n' AND DATE_SCHEDULED != 'N') OR
       (DATE_WINDOW != 'n' AND DATE_WINDOW != 'N'));

before adding constraint 'CHK_DATES_VALID' i need to check is that constrain existing or not please guide me to make this condation .

score 8 · Accepted Answer · 2011-05-29 08:15:53Z

8

You cannot use IF like that.

You need to check the system view ALL_CONSTRAINTS in order to find out if the constraint if already defined:

DECLARE 
  num_rows integer;
BEGIN
   SELECT count(*)
      INTO num_rows
   FROM all_constraints
   WHERE constraint_name = 'CHK_DATES_VALID';

   IF num_rows = 0 THEN 
       EXECUTE IMMEDIATE 'ALTER TABLE the_table 
         ADD CONSTRAINT CHK_DATES_VALID
         CHECK ((to_upper(DATE_NORMAL) != ''N'') OR
                (to_upper(DATE_SCHEDULED) != ''N'') OR
                (to_upper(DATE_WINDOW) != ''N''))';
   END IF;
END;
/

The EXECUTE IMMEDIATE is necessary because you cannot run DDL directly in a PL/SQL block.

A much easier solution would be to simply add the constraint and catch any error that occurs.

edited May 29, 2011 at 8:15

answered May 28, 2011 at 20:07

user330315

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

3 Comments

jaiswal Over a year ago

hi its taking time because i have thousand of table and thousand of constraints , plz tell me any other soln

user330315 Over a year ago

There is no other solution to add a constraint

masterwok Over a year ago

Thanks for explaining why execute immediate is necessary.

Collectives™ on Stack Overflow

check before adding Constraint in table (oracle)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related