Multiple Constraints in Table: How to Get All Violations

Multiple constraints in table: How to get all violations?

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.

Oracle: How to get all possible constraint violations?

I have found a simple solution for me:

spool 'disabled_constraint_violations.txt';
set serverout on size unlimited
DECLARE
sql_enable varchar2(400);
sql_disable varchar2(400);
BEGIN
FOR c IN (select OWNER, TABLE_NAME, CONSTRAINT_NAME from all_constraints where STATUS='DISABLED') LOOP
BEGIN
sql_enable := ' alter table ' || c.owner||'.'||c.table_name || ' enable constraint ' || c.constraint_name;
execute immediate sql_enable ;
sql_disable := ' alter table ' || c.owner||'.'||c.table_name || ' disable constraint ' || c.constraint_name;
execute immediate sql_disable ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('table: '||c.table_name||' || constraint: '||c.constraint_name||' || error message: '||sqlerrm);
END;
END LOOP;
END;
set serveroutput off
spool off

Checking for multiple child constraint violations in Hibernate/JPA

I found a way to accomplish my end result, although it was through means I did not expect.

In order to accomplish my goal, I would need to use nested transactions and SavePoints. At the end of the day, that implementation would still produce invalid data in my database, because during the time it takes to find an error in a child, some of the other children may have already been persisted and a consumer of my database would be unaware that the parent and all it's children were about to be deleted due to one or more bad entities (parents or children).

My solution:

I implemented a validator to validate all of the parents and children before going to persist them. The drawback to this method is that I have to annotate constraints on my entity fields, but double validation is never a bad thing.

The answer to my original question:

This is impossible to due with my version of Hibernate, unless I implemented custom SavePoint functionality to support nested transactions.

Get all constraint errors when inserting data from another table

There are couple of options:

  1. If you want to catch row level information, you have to go for cursors or while loop and try to insert each row in TRY CATCH block and see if you are getting any error, and log the same.

  2. Create another table similar to main table(say, MainCheckTable) with all constraints and disable all the constraints and load the data.

Now, you can leverage DBCC CHECKCONSTRAINTS to see all the constraint violations.Read more on this .

USE DBName;
DBCC CHECKCONSTRAINTS(MainCheckTable) WITH ALL_CONSTRAINTS;

How to find the record that violate unique key constraint?

If you don't have a unique key on col_b, col_c, col_d of table_b, this will result in a violation when copying over. You can identify problematic rows with a query like this:

SELECT col_b, col_c, col_d
FROM table_b
GROUP BY col_b, col_c, col_d
HAVING COUNT(*) > 1

A similar query can be run on table_a joined to table_b, but the specific queries to run will depend on which columns will be updated in table_a. For the insert case, a useful technique might be to use a MINUS between table_a and the proposed inserted row.

PostgreSQL - Multiple constraints

The problem with your approach is that your constraints are not full.
For example:

CONSTRAINT field_a_check CHECK (thematic::text ~~ 'A'::text AND field_a IS NOT NULL),

The constraint says "the record is ok if thematic contains 'A' and field_a is not empty". That means the record is not OK otherwise (if it does not contain 'A'). If you appended your checks with "OK otherwise" you could have several of them - no problem:

CONSTRAINT field_a_check CHECK (thematic::text ~~ 'A'::text AND field_a IS NOT NULL OR NOT thematic::text ~~ 'A'::text)

As to why the parenthesis are removed - it's because they are not needed. The AND operator has priority over OR, so the expressions are the same with or without parenthesis.

You are welcome to check the solution at db<>fiddle

Constraint violation when merging into table

The EXASolution user manual says:

The content of an identity column applies to the following rules:

  • If you specify an explicit value for the identity column while inserting a row, then this value is inserted.
  • In all other cases monotonically increasing numbers are generated by the system, but gaps can occur between the numbers.

and

You should not mistake an identity column with a constraint, i.e. identity columns do not guarantee unique values. But the values are unique as long as values are inserted only implicitly and are not changed manually.

You've put a primary key constraint on your identity column, so it must be unique. Since you are getting duplicates from your merge, either (a) you have, at some point, provided explicit values as in the first bullet above or updated a value manually, and the monotonically increasing sequence has reached a point where it is clashing with those existing values; or (b) there's a bug in their merge. The former seems more likely.

You can look at recently inserted value if you have one, or do a temporary insert of a new row (with merge) to see if it will create a row successfully, and if so whether you already have ID values higher than the one it allocates for that new row. If there are no higher values already, and insert works and merge continues to fail consistently, then it sounds like something you'd need to raise with EXASolution.



Related Topics



Leave a reply



Submit