Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

Where are contraints stored

Hello,
I need to use a commercial tool that creates tables in DB.
When I checked directly in Oracle the table created I could not  see any contraint on it (via DBA console, No PK, no unique). However, if I insert duplicated row, the message is "MYSCHEMA.R36_...."  contraint violated (I cannot remember exactly). I checked views SYS.ALL_CONSTRAINTS and SYS.DBA_CONTRAINTS for the reported name of the constraint, but nothing (I checked all contraints for MYSCHEMA so no mistyped contrain name).
What are other data dictionary views/tables to get constraints?

0
matejak
Asked:
matejak
1 Solution
 
BobMcCommented:
As far as I am aware, there are only two families of dictionary views that hold constraint info

*_CONSTRAINTS          - details about constraints
*_CONS_COLUMNS       - details of columns involved in constraint definitions

However if you create a unqiue index, it wont show up in either, but entering a duplicate value will fail with a ORA-00001: unique constraint (SCHEMA.INDEX) violated

HTH
Bob
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
select p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
from user_constraints p, user_constraints c
where (p.constraint_type = 'P' OR p.constraint_type = 'U')
and c.constraint_type = 'R'
and p.constraint_name = c.r_constraint_name
/

may be this query helpful in a convinient way.

LeoAhmad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now