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 .

Answer 1

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.