Multiple constraints in table: How to get all violations?

Viewed 2609

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?

3 Answers
Related