表中的多个约束：如何获取所有违规？ [英] Multiple constraints in table: How to get all violations?

问题描述

我在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值（1，null，null，2）; 
 
 ORA-01400：不能向（USER_4_8483C。A_TABLE_TEST。COL_2）中插入NULL 
  

我想得到这样的东西：

 列COL_2：不能插入NULL 
列COL_3：不能插入NULL 
 
这也是足够的：
列COL_2：无效
列COL_3：无效
  

当然，我可以写一个触发器并单独检查每一列，但我喜欢约束而不是触发器，它们更容易维护，

解决方案

在同时我发现一个使用延迟约束的精益解决方案：

  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值（1，null，null，2）; 
 
 DECLARE 
 CHECK_CONSTRAINT_VIOLATED EXCEPTION; 
 PRAGMA EXCEPTION_INIT（CHECK_CONSTRAINT_VIOLATED，-2290）; 
 
 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 THEN 
 DBMS_OUTPUT.PUT_LINE（'Column'|| aCon.COLUMN_NAME ||'violated'）; 
 END; 
 END LOOP; 
 END; 
  

它适用于任何检查约束（不仅 NOT NULL ）。检查 FOREIGN KEY 约束也应该工作。

添加/修改/删除约束不需要任何进一步的维护。

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?

解决方案

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

    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 THEN
        DBMS_OUTPUT.PUT_LINE('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.

这篇关于表中的多个约束：如何获取所有违规？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！