CODEWITHSUNDEEP
sqloracle-databaseconstraints
Jeremy
Jeremy

Reputation: 5435

Learn more about the nature of a constraint?

A piece of legacy code I'm running that does some oracle SQL is violating a constraint called REF_REQUEST.

I look up this constraint by doing:

select * from all_constraints where constraint_name='REF_REQUEST'

This tells me that the constraint_type is 'R', and it gives me the table name of CORRESPONDENCE.

However, I still don't know... what value I'm missing from CORRESPONDENCE, or where I'm trying to insert that is causing the issue, and what column is relevant from each. How can I learn this information by querying the dB?

Upvotes: 2

Views: 184

Answers (2)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

Use get_ddl method in dbms_metadata package to get more details about the constraint

SELECT CAST(DBMS_METADATA.GET_DDL('CONSTRAINT','REF_REQUEST','CERTIFICATION') AS VARCHAR2(4000)) 
FROM DUAL

That should tell you what columns the constraint is acting on

One of the overloaded methods accepts the schema name, so you can pass it as a parameter.

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Further reading:

Upvotes: 2

DCookie
DCookie

Reputation: 43533

Alternatively, you know it's a referential integrity constraint based on your select from all_constraints (constraint_type = R), so you can just query all_cons_columns for your answer:

SELECT table_name, column_name
  FROM all_cons_columns
 WHERE constraint_name = 'REF_REQUEST'
 ORDER by position;

Upvotes: 3

Related Questions