Solved

Where are contraints stored

Posted on 2004-08-12
2
712 Views
Last Modified: 2013-12-11
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
Comment
Question by:matejak
2 Comments
 
LVL 7

Accepted Solution

by:
BobMc earned 125 total points
ID: 11789211
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
 
LVL 14

Expert Comment

by:leoahmad
ID: 11789456
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now