Working with Oracle databases can be daunting to developers and analysts who lack a deep understanding of relational models and SQL. One excellent example of a mystifying pitfall is the dreaded ORA-02292: integrity constraint error. This error often occurs when users are attempting to DELETE rows in a well-normalized schema. For example’s sake, I’ll focus on Oracle Transportation Management (OTM), which is where I’ve spent much of my time over the last six months.
Given the T in OTM, configurators and analysts often focus on orders and shipments. After discovering the backdoor SQL Servlet or connecting via SQL Developer, new configurators and analysts may think they can simply delete rows from the ORDER_RELEASE or SHIPMENT table to remove unwanted data. This often goes something like this…
> DELETE FROM order_release
WHERE order_release_gid = 'CUSTOMER.ORDER_NUMBER'
Error starting at line 1 in command:
DELETE FROM order_release WHERE order_release_gid = 'CUSTOMER.ORDER_NUMBER'
Error report: SQL Error: ORA-02292: integrity constraint (GLOGOWNER.FK_SSUL_OR_GID) violated - child record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause: attempted to delete a parent key value that had a foreign dependency.
*Action: delete dependencies first then parent or disable constraint. Oops. The database is telling us that there are records in a different table in the database that point to this record. If we delete this record, the broken reference will confuse the database. But how do we know where the reference is? There are nearly 2000 tables in OTM, so looking through them one-by-one isn’t an option…
At this point, most analysts will learn not to try direct SQL deletions for tables. If you ask, they’ll tell you it’s not possible to do outside of the OTM UI. While it’s true that the UI or application should be used for most common deletion tasks, this misunderstanding of database constraints and normalization can promulgate into broader misunderstandings of feasibility and relational models. So let’s back up and understand what actually went on in this ORA-2292 error above. How do we know where the dependencies on ORDER_RELEASE are?
Oracle databases contain a special table that stores all constraints in the database, conveniently named ALL_CONSTRAINTS. Looking at ALL_CONSTRAINTS, you’ll see that the first two fields are OWNER and CONSTRAINT_NAME. To find the constraint violated in ORA-2292 above, look for the following portion of the message – “integrity constraint (GLOGOWNER.FK_SSUL_OR_GID) violated.” In this error string, GLOGOWNER corresponds to the constraint owner, and FK_SSUL_OR_GID corresponds to the constraint name. So, where does this get us?
> SELECT owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name
FROM all_constraints
WHERE owner='GLOGOWNER'
AND constraint_name='FK_SSUL_OR_GID';
owner | constraint_name | constraint_type | table_name | r_owner | r_constraint_name
GLOGOWNER | FK_SSUL_OR_GID | R | S_SHIP_UNIT_LINE | GLOGOWNER | PK_ORDER_RELEASE OK, but what does this mean? In a nutshell, the table S_SHIP_UNIT_LINE has a column that must match up with the PK_ORDER_RELEASE constraint…yes, another constraint to look up.
> SELECT owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name
FROM all_constraints
WHERE owner='GLOGOWNER'
AND constraint_name='PK_ORDER_RELEASE';
owner | constraint_name | constraint_type | table_name | r_owner | r_constraint_name
GLOGOWNER | PK_ORDER_RELEASE | P | ORDER_RELEASE | |
> SELECT * FROM all_cons_columns
WHERE owner='GLOGOWNER'
AND constraint_name = 'PK_ORDER_RELEASE';
owner | constraint_name | table_name | column_name | position
GLOGOWNER | PK_ORDER_RELEASE | ORDER_RELEASE | ORDER_RELEASE_GID | 1 This one isn’t so bad – PK constraints in OTM’s schema always map to the corresponding GID, or unique identifier for a record. So, to summarize the ORA-2292 error above, we can’t delete the record from ORDER_RELEASE because a record from S_SHIP_UNIT_LINE is tied to that ORDER_RELEASE_GID.
Now, if that makes this seem easy, stop yourself – it’s not. This is just one of many possible integrity constraints that an ORDER_RELEASE may have. Let’s try to summarize the above process into a single query. Given a constraint, how would we find the tables and columns that are tied together?
> SELECT ac.owner AS left_owner, ac.constraint_name AS left_name, ac.table_name AS left_table, acc.column_name AS left_column, acc.position AS left_position, acr.owner AS right_owner, acr.constraint_name AS right_name, acr.table_name AS right_table, accr.column_name AS right_column, accr.position AS right_position
FROM all_constraints ac
JOIN all_cons_columns acc ON ac.constraint_name=acc.constraint_name
JOIN all_constraints acr ON ac.r_constraint_name=acr.constraint_name
JOIN all_cons_columns accr ON acr.constraint_name=accr.constraint_name
WHERE ac.owner='GLOGOWNER'
AND ac.constraint_name='FK_SSUL_OR_GID';
LEFT_OWNER | LEFT_NAME | LEFT_TABLE | LEFT_COLUMN | LEFT_POSITION | RIGHT_OWNER | RIGHT_NAME | RIGHT_TABLE | RIGHT_COLUMN | RIGHT_POSITION
GLOGOWNER | FK_SSUL_OR_GID | S_SHIP_UNIT_LINE | ORDER_RELEASE_GID | 1 | GLOGOWNER | PK_ORDER_RELEASE | ORDER_RELEASE | ORDER_RELEASE_GID | 1 Great! While the query isn’t small, the only parts you should need to change are the two final WHERE constraints. The output is easy to understand – LEFT_TABLE.LEFT_COLUMN references RIGHT_TABLE.RIGHT_COLUMN.
For extra credit, how would we find every such dependency between a table and other tables? While this may seem much harder than the original query, we’ve already joined the relevant table.
> SELECT ac.owner AS left_owner, ac.constraint_name AS left_name, ac.table_name AS left_table, acc.column_name AS left_column, acc.position AS left_position, acr.owner AS right_owner, acr.constraint_name AS right_name, acr.table_name AS right_table, accr.column_name AS right_column, accr.position AS right_position
FROM all_constraints ac
JOIN all_cons_columns acc ON ac.constraint_name=acc.constraint_name
JOIN all_constraints acr ON ac.r_constraint_name=acr.constraint_name
JOIN all_cons_columns accr ON acr.constraint_name=accr.constraint_name
WHERE acr.table_name='ORDER_RELEASE';
...
(37 rows returned)
There – 37 first-degree constraints on the ORDER_RELEASE table. Now, if only each of these 37 tables didn’t have their own integrity constraints…but more to come on this in a later post! In the meantime, a few things to remember or bookmark:
- While I’ve focused on the OTM example in this post, the processes in this post are applicable to any Oracle database.
- To learn more about the constraint and constraint column tables, read the documentation: ALL_CONSTRAINTS ; ALL_CONS_COLUMNS.
- These constraints can extend “recursively” through tables. For example, the S_SHIP_UNIT_LINE table depends on the PK_S_SHIP_UNIT_LINE table.
- FKs build a real, directed network! For real-world data models, these networks can be substantial and complex.
