how to look up constraint definition 
Author Message
 how to look up constraint definition

In SQLplus, if I try and do something which contradicts a
foreign key constraint, I get a message like this:

----------------------------------------------------------------------
On ORATSTA, DE_LND_CATCH_SRC_REC table has a foreign key constraint
(DLR_DCS_FK):

HARVEST_V2_0.DE_LND_CATCH_SRC_REC(DCS_DCST_SRC_CDE) REFERENCES
HARVEST_V2_0.DATA_COLLECTN_SRC(DCST_SRC_CDE)
----------------------------------------------------------------------

How would I pursuade Oracle to give me the second half of that
message,
given the constraint name?

In other words, how do I look up the complete definition of a
constraint, from the constraint name?



Mon, 21 Feb 2005 08:20:34 GMT
 how to look up constraint definition

Hi Matthew,

Try the Data Dictionary View USER_CONSTRAINTS or ALL_CONSTARINTS.

Type DESC USER_CONSTRAINTS in SQL and select the information You want from
it.
The views give You all contraints on a table when You specify WHERE
TABLE_NAME = ...

Jan



Quote:
> In SQLplus, if I try and do something which contradicts a
> foreign key constraint, I get a message like this:

> ----------------------------------------------------------------------
> On ORATSTA, DE_LND_CATCH_SRC_REC table has a foreign key constraint
> (DLR_DCS_FK):

> HARVEST_V2_0.DE_LND_CATCH_SRC_REC(DCS_DCST_SRC_CDE) REFERENCES
> HARVEST_V2_0.DATA_COLLECTN_SRC(DCST_SRC_CDE)
> ----------------------------------------------------------------------

> How would I pursuade Oracle to give me the second half of that
> message,
> given the constraint name?

> In other words, how do I look up the complete definition of a
> constraint, from the constraint name?



Mon, 21 Feb 2005 15:53:32 GMT
 how to look up constraint definition

Quote:

>In SQLplus, if I try and do something which contradicts a
>foreign key constraint, I get a message like this:

>----------------------------------------------------------------------
>On ORATSTA, DE_LND_CATCH_SRC_REC table has a foreign key constraint
>(DLR_DCS_FK):

>HARVEST_V2_0.DE_LND_CATCH_SRC_REC(DCS_DCST_SRC_CDE) REFERENCES
>HARVEST_V2_0.DATA_COLLECTN_SRC(DCST_SRC_CDE)
>----------------------------------------------------------------------

>How would I pursuade Oracle to give me the second half of that
>message,
>given the constraint name?

>In other words, how do I look up the complete definition of a
>constraint, from the constraint name?

Joining user_constraints with itself, using the condition
c1.constraint_name = c.r_constraint_name
and further more joining with user_cons_columns

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address



Mon, 21 Feb 2005 16:51:39 GMT
 how to look up constraint definition

Quote:

> In SQLplus, if I try and do something which contradicts a
> foreign key constraint, I get a message like this:

> ----------------------------------------------------------------------
> On ORATSTA, DE_LND_CATCH_SRC_REC table has a foreign key constraint
> (DLR_DCS_FK):

> HARVEST_V2_0.DE_LND_CATCH_SRC_REC(DCS_DCST_SRC_CDE) REFERENCES
> HARVEST_V2_0.DATA_COLLECTN_SRC(DCST_SRC_CDE)
> ----------------------------------------------------------------------

> How would I pursuade Oracle to give me the second half of that
> message,
> given the constraint name?

> In other words, how do I look up the complete definition of a
> constraint, from the constraint name?

Join the dba_constaints dictionary view to dba_cons_columns to see the
referenced table and columns

HTH -- Mark D Powell --



Mon, 21 Feb 2005 21:38:06 GMT
 how to look up constraint definition
Thanks for the suggestions.

So, procedurally speaking:

- I look up the constraint name in ALL_CONSTRAINTS, and
  get R_CONSTRAINT_NAME
- I look up the constraing name in ALL_CONS_COLUMNS to
  get the table and column to which the constraint applies
- I look up the R_CONSTRAINT_NAME in ALL_CONS_COLUMNS
  to get the table and column where the foreign key must exist.
  (so I presume that 'R_' in 'R_CONSTRAINT_NAME' stands for
  'Reference')



Tue, 22 Feb 2005 00:09:24 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SQL 7 and concurrent look-ups

2. look ups

3. Look ups

4. Problems with look-ups

5. Look-ups on two fields

6. Help for Look Ups

7. Slow Database look ups

8. Table constraint definition

9. Constraint definition

10. Looking for Field-Type definitions

11. what is multivalue...? (looking for a definition)

12. Looking for definition of OODB (an OODB manifesto)


 
Powered by phpBB® Forum Software