표의 다중 제약 조건:모든 위반을 가져오는 방법?

표의 다중 제약 조건:모든 위반을 가져오는 방법?

Oracle에 몇 가지 제약이 있는 테이블이 있습니다.새 레코드를 삽입할 때 모든 제약 조건이 유효하지 않으면 Oracle은 "첫 번째" 오류만 발생시킵니다.내 기록을 위반하는 모든 것을 얻으려면 어떻게 해야 합니까?

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

저는 다음과 같은 것을 얻고 싶습니다.

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

물론 트리거를 작성하고 각 열을 개별적으로 확인할 수 있지만, 트리거보다는 제약 조건을 선호합니다. 이 제약 조건은 유지 관리가 쉽고 수동으로 코드를 작성할 필요가 없습니다.

감 잡히는 게 없어요?

가능한 모든 제약 조건 위반을 직접 보고할 수 있는 방법은 없습니다.Oracle이 첫 번째 제약 조건 위반에 걸려 넘어졌을 때 해당 제약 조건이 지연되지 않는 한 추가 평가가 불가능하고 문이 실패하기 때문입니다.log errors절이 DML 문에 포함되었습니다.하지만 주의해야 할 것은log errors절은 가능한 모든 제약 조건 위반을 탐지할 수 없습니다. 먼저 하나만 기록하십시오.

가능한 방법 중 하나는 다음과 같습니다.

1. 만들다exceptions테이블. 실행하면 됩니다.ora_home/rdbms/admin/utlexpt.sql대본.테이블의 구조는 매우 간단합니다.
2. 모든 테이블 구속조건 비활성화;
3. DML 실행;
4. 모든 제약 조건 사용exceptions into <<exception table name>>절실행한 경우utlexpt.sql스크립트, 예외가 저장될 테이블의 이름은 다음과 같습니다.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

위의 쿼리는 열 이름과 문제가 있는 레코드의 행 ID를 제공합니다.로우이드가 가까이 있으면 제약 조건 위반을 일으키는 레코드를 찾아 수정하고 제약 조건을 다시 활성화하는 데 문제가 없을 것입니다.

다음은 생성에 사용된 스크립트입니다.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

삽입 전 트리거를 구현하여 관심 있는 모든 조건을 루프해야 합니다.

데이터베이스의 관점에서 상황을 생각해 보세요.당신이 할 때insert데이터베이스는 기본적으로 삽입을 성공적으로 완료하거나 어떤 이유로 실패하는 두 가지 작업(일반적으로 제약 조건 위반)을 수행할 수 있습니다.

데이터베이스는 가능한 한 빨리 진행하고 불필요한 작업을 수행하지 않기를 원합니다.첫 번째 불만 사항 위반을 발견하면 레코드가 데이터베이스에 들어가지 않는다는 것을 알 수 있습니다.따라서, 엔진은 현명하게 오류를 반환하고 더 이상의 제약 조건 확인을 중지합니다.엔진이 전체 위반 목록을 가져올 이유가 없습니다.

그동안 지연된 제약 조건을 사용하여 희박한 솔루션을 찾았습니다.

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제약 조건도 작동해야 합니다.

제약 조건 추가/수정/삭제에는 추가 유지 관리가 필요하지 않습니다.

언급URL : https://stackoverflow.com/questions/20761268/multiple-constraints-in-table-how-to-get-all-violations