Несколько ограничений в таблице: как получить все нарушения?

Ответ 1

Тем временем я нашел экономное решение с использованием отложенных ограничений:

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;

Работает с любыми проверочными ограничениями (не только NOT NULL). Проверка FOREIGN KEY Ограничение также должно работать.

Добавление/изменение/удаление ограничений не требует дальнейшего обслуживания.

Ответ 2

Нет простого способа сообщить о всех возможных нарушениях ограничений. Потому что, когда Oracle наткнется на первое нарушение ограничения, дальнейшая оценка невозможна, утверждение завершается с ошибкой, если только это ограничение не отложено, или предложение log errors было включено в оператор DML. Но следует отметить, что предложение log errors не сможет поймать все возможные нарушения ограничений, просто записывает первый.

Как один из возможных способов:

• создать таблицу exceptions. Это можно сделать, выполнив ora_home/rdbms/admin/utlexpt.sql script. Структура таблицы довольно проста:
• отключить все ограничения таблицы;
• выполнить DMLs;
• включить все ограничения с предложением exceptions into <<exception table name>>. Если вы выполнили utlexpt.sql script, имя табличных исключений будет сохранено в формате exceptions.

Таблица тестов:

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

Попробуйте выполнить оператор insert:

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")

Отключить все ограничения таблицы:

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; 

Повторите попытку с предыдущим запросом insert:

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

1 rows inserted.

commit;

Теперь включите ограничения таблицы и сохраните исключения, если они есть, в таблице exceptions:

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; 

Проверьте таблицу exceptions:

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

Нарушены два ограничения. Чтобы узнать имена столбцов, просто обратитесь к user_cons_columns представлению словаря данных:

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

В приведенном выше запросе даются имена столбцов и ряды проблемных записей. Имея ряды под рукой, не должно быть проблем, чтобы найти те записи, которые вызывают нарушение ограничений, исправлять их и снова включать ограничения еще раз.

Вот script, который использовался для создания операторов alter table для включения и отключения ограничений:

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

Ответ 3

Вам понадобится реализовать триггер before-insert, чтобы выполнить все условия, о которых вы заботитесь.

Подумайте о ситуации с точки зрения базы данных. Когда вы выполняете insert, база данных может в основном выполнять две функции: завершите вставку успешно или по какой-то причине потерпите неудачу (обычно это нарушение ограничения).

База данных хочет действовать так быстро, как возможно, и не делать ненужной работы. Как только он обнаружил первое нарушение жалобы, он знает, что запись не входит в базу данных. Таким образом, двигатель с мудростью возвращает ошибку и перестает проверять дальнейшие ограничения. Нет причин для того, чтобы двигатель мог получить полный список нарушений.