Query data that does not fulfill constraints in table in Oracle DB

constraintoracleschema

In Oracle I have a lot of tables in a given schema. Some of the table constraints in the schema have been disabled.

I have to know for all tables in a given schema with any of the constraints deactivated, what are the (data) rows that would not fulfil the deactivated constraints.

Question

Is there a way to easily collect all the rows that would produce an error if I were to decide to enable the constraints again?

So far, I would try to go constraint by constraint, create a query for each table and run the query. But I wonder whether there is anything that may do the work quickly and less error prone. If possible, the solution should use the information available in view USER_CONSTRAINTS or any similar one.

I have checked the constraint documentation in Oracle, in which the constraint clause may use ENABLE NOVALIDATE, which allows for having non-compliant rows of data for the existing values until they are modified.

However, I am looking for a solution that would allow me to fix the data before I re-enable the constraints.

Best Answer

Reporting Constraint Exceptions

You must create an appropriate exceptions report table to accept information from the EXCEPTIONS option of the ENABLE clause before enabling the constraint. You can create an exception table by executing the UTLEXCPT.SQL script or the UTLEXPT1.SQL script.

The following statement attempts to validate the PRIMARY KEY of the dept table, and if exceptions exist, information is inserted into a table named EXCEPTIONS:
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
If duplicate primary key values exist in the dept table and the name of the PRIMARY KEY constraint on dept is sys_c00610, then the following query will display those exceptions:
SELECT * FROM EXCEPTIONS;
The following exceptions are shown:
fROWID               OWNER      TABLE_NAME      CONSTRAINT
------------------  ---------  --------------  -----------
AAAAZ9AABAAABvqAAB  SCOTT      DEPT            SYS_C00610 
AAAAZ9AABAAABvqAAG  SCOTT      DEPT            SYS_C00610

Related Solutions

How to retrieve foreign key constraints data

After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:

SELECT
    CONS.CONSTRAINT_NAME,
    CONS.TABLE_NAME,
    COLS.COLUMN_NAME,
    CONS.R_CONSTRAINT_NAME,
    CONS_R.TABLE_NAME R_TABLE_NAME,
    COLS_R.COLUMN_NAME R_COLUMN_NAME

FROM USER_CONSTRAINTS CONS
    LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
    LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
    LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME

-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R'

ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME

Is it any potential problem with having 2 constraints suppored by one index in Oracle

As Nat has noted your UQ_ACTION constraint duplicates the primary key constraint. Based on your FK usage into this table, your primary key is (action_id, action_type_id). As such it is redundant and should be removed. If you wish your primary key to be action_id alone, then your foreign keys must reference only action_id.

I believe you were correct in modeling action_id as the primary key. In this case change your child references. (Having the action type id in the foreign key violates normal form and forces redundant information into the referencing tables.). Child tables of your action table would have the action type id in their key if they apply to multiple types.

You may want a UK on action_id, but it is not necessary. If you have such a UK with a corresponding index, then I would reverse the order on the index containing (action_type_id, action_id). This will allow fast retrieval by action_id alone and by action_type_id where statistics indicate an index is useful.

However, to answer your question in cases where an index can be used for multiple constraints, no there is no penalty. In fact, the opposite is the case.

Maintaining indexes can be a relatively expensive operation. Adding redundant indexes does have a penalty. Each insert, update, or delete that changes the column in the redundant index will require additional work to update the index.

It appears you have recognized the value of not creating unused indexes. While common, foreign key indexes are not necessary in many cases. It is a good idea to have an index if you do deletes from the referenced table. A surrogate index beginning with the foreign key may result in more efficient queries. If you do frequent lookups by action_type_id, the index would be useful. It might be more useful to have a multi-column index with other values used to filter the result set.

Question

Best Answer

Related Solutions

How to retrieve foreign key constraints data

Is it any potential problem with having 2 constraints suppored by one index in Oracle

Related Question