Multiple constraints in table: How to get all violations?

Question

I have a table in Oracle with several constraints. When I insert a new record and not all constraints are valid, then Oracle raise only the "first" error. How to get all violations of my record?

CREATE TABLE A_TABLE_TEST (
  COL_1 NUMBER NOT NULL,
  COL_2 NUMBER NOT NULL,
  COL_3 NUMBER NOT NULL,
  COL_4 NUMBER NOT NULL
);

INSERT INTO A_TABLE_TEST values (1,null,null,2);

ORA-01400: cannot insert NULL into ("USER_4_8483C"."A_TABLE_TEST"."COL_2")

I would like to get something like this:

Column COL_2: cannot insert NULL
Column COL_3: cannot insert NULL

This would be also sufficient:
Column COL_2: not valid
Column COL_3: not valid

Of course I could write a trigger and check each column individually, but I like to prefer constraints rather than triggers, they are easier to maintain and don't require manually written code.

Any idea?

Answer 1

There no straightforward way to report all possible constraint violations. Because when Oracle stumble on first violation of a constraint, no further evaluation is possible, statement fails, unless that constraint is deferred one or the log errors clause has been included in the DML statement. But it should be noted that log errors clause won't be able to catch all possible constraint violations, just records first one.

As one of the possible ways is to:

1. create exceptions table. It can be done by executing ora_home/rdbms/admin/utlexpt.sql script. The table's structure is pretty simple;
2. disable all table constraints;
3. execute DMLs;
4. enable all constraints with exceptions into <<exception table name>> clause. If you executed utlexpt.sql script, the name of the table exceptions are going to be stored would be exceptions.

Test table:

create table t1(
  col1 number not null,
  col2 number not null,
  col3 number not null,
  col4 number not null
);

Try to execute an insert statement:

insert into t1(col1, col2, col3, col4)
  values(1, null, 2, null);

Error report -
SQL Error: ORA-01400: cannot insert NULL into ("HR"."T1"."COL2")

Disable all table's constraints:

alter table T1 disable constraint SYS_C009951;     
alter table T1 disable constraint SYS_C009950;     
alter table T1 disable constraint SYS_C009953;     
alter table T1 disable constraint SYS_C009952; 

Try to execute the previously failed insert statement again:

insert into t1(col1, col2, col3, col4)
  values(1, null, 2, null);

1 rows inserted.

commit;

Now, enable table's constraints and store exceptions, if there are any, in the exceptions table:

alter table T1 enable constraint SYS_C009951 exceptions into exceptions; 
alter table T1 enable constraint SYS_C009950 exceptions into exceptions; 
alter table T1 enable constraint SYS_C009953 exceptions into exceptions; 
alter table T1 enable constraint SYS_C009952 exceptions into exceptions; 

Check the exceptions table:

column row_id     format a30;
column owner      format a7;
column table_name format a10;
column constraint format a12;

select *
  from exceptions 

ROW_ID                         OWNER   TABLE_NAME CONSTRAINT 
------------------------------ ------- -------    ------------
AAAWmUAAJAAAF6WAAA             HR      T1         SYS_C009951  
AAAWmUAAJAAAF6WAAA             HR      T1         SYS_C009953

Two constraints have been violated. To find out column names, simply refer to user_cons_columns data dictionary view:

column table_name   format a10;
column column_name  format a7;
column row_id       format a20;

select e.table_name
     , t.COLUMN_NAME
     , e.ROW_ID
  from user_cons_columns t
  join exceptions e
    on (e.constraint = t.constraint_name)


TABLE_NAME COLUMN_NAME ROW_ID             
---------- ----------  --------------------
T1         COL2        AAAWmUAAJAAAF6WAAA   
T1         COL4        AAAWmUAAJAAAF6WAAA

The above query gives us column names, and rowids of problematic records. Having rowids at hand, there should be no problem to find those records that cause constraint violation, fix them, and re-enable constraints once again.

Here is the script that has been used to generate alter table statements for enabling and disabling constraints:

column cons_disable format a50
column cons_enable format a72

select 'alter table ' || t.table_name || ' disable constraint '|| 
        t.constraint_name || ';' as cons_disable
     , 'alter table ' || t.table_name || ' enable constraint '|| 
        t.constraint_name || ' exceptions into exceptions;' as cons_enable
  from user_constraints t
where t.table_name = 'T1'
order by t.constraint_type

Answer 2

You would have to implement a before-insert trigger to loop through all the conditions that you care about.

Think about the situation from the database's perspective. When you do an insert, the database can basically do two things: complete the insert successfully or fail for some reason (typically a constraint violation).

The database wants to proceed as quickly as possibly and not do unnecessary work. Once it has found the first complaint violation, it knows that the record is not going into the database. So, the engine wisely returns an error and stops checking further constraints. There is no reason for the engine to get the full list of violations.

Answer 3

In the meantime I found a lean solution using deferred constraints:

CREATE TABLE A_TABLE_TEST (
  COL_1 NUMBER NOT NULL DEFERRABLE INITIALLY DEFERRED,
  COL_2 NUMBER NOT NULL DEFERRABLE INITIALLY DEFERRED,
  COL_3 NUMBER NOT NULL DEFERRABLE INITIALLY DEFERRED,
  COL_4 NUMBER NOT NULL DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO A_TABLE_TEST values (1,null,null,2);    

DECLARE
    CHECK_CONSTRAINT_VIOLATED EXCEPTION;
    PRAGMA EXCEPTION_INIT(CHECK_CONSTRAINT_VIOLATED, -2290);

    REF_CONSTRAINT_VIOLATED EXCEPTION;
    PRAGMA EXCEPTION_INIT(REF_CONSTRAINT_VIOLATED , -2292);


    CURSOR CheckConstraints IS
    SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME
    FROM USER_CONSTRAINTS
        JOIN USER_CONS_COLUMNS USING (TABLE_NAME, CONSTRAINT_NAME)
    WHERE TABLE_NAME = 'A_TABLE_TEST'
        AND DEFERRED = 'DEFERRED'
        AND STATUS = 'ENABLED';
BEGIN
    FOR aCon IN CheckConstraints LOOP
    BEGIN
        EXECUTE IMMEDIATE 'SET CONSTRAINT '||aCon.CONSTRAINT_NAME||' IMMEDIATE';
    EXCEPTION
        WHEN CHECK_CONSTRAINT_VIOLATED OR REF_CONSTRAINT_VIOLATED  THEN
        DBMS_OUTPUT.PUT_LINE('Constraint '||aCon.CONSTRAINT_NAME||' at Column '||aCon.COLUMN_NAME||' violated');
    END;
    END LOOP;
END;

It works with any check constraint (not only NOT NULL). Checking FOREIGN KEY Constraint should work as well.

Add/Modify/Delete of constraints does not require any further maintenance.