Home » SQL & PL/SQL » SQL & PL/SQL » Refrential Integrity Query
Refrential Integrity Query [message #241730] Wed, 30 May 2007 08:57 Go to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
All,

Oracle 10.2.0.3.0 Enterprise Edition.

Can anyone please provide a query for selecting all the referential integirty and constraints on a table. I have jhad a look at the manuals and know about DBA_CONSTRAINTS, DBA_CONS_COLUMNS etc etc but couldn't find an easy way to select all constrainst and referential integrity on 1 table in 1 schema including the names of the tables which contain the FK, columns etc etc.

Can anyone help please?

Thanks in advance,

Ken.
Re: Referential Integrity Query [message #241732 is a reply to message #241730] Wed, 30 May 2007 09:07 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
select * from all_constraints where table_name = <table_name>


The "R" Constriant_Type is referential.
Re: Refrential Integrity Query [message #241733 is a reply to message #241730] Wed, 30 May 2007 09:09 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You can also use DBMS_METADATA. For examples, see http://www.orafaq.com/forum/t/9835/0/
Re: Refrential Integrity Query [message #241737 is a reply to message #241730] Wed, 30 May 2007 09:18 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
compcoder,

If only it was that simple. Maybe you have misunderstood the question. The query which you have supplied would return the primary and foreign key constrainst for that table and that table only. Here is an example of what I require.

I have a table called Orders with a P, 1 R and 1 C constraint. I would like to find out what tables and columns refrence my P constraint and what tables and columns are referenced by the F constraint on the Orders table?

Hope the above makes sense.

Ken.
Re: Refrential Integrity Query [message #241739 is a reply to message #241730] Wed, 30 May 2007 09:24 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Frank,

Thanks for the link. This will give me the DDL for the F constraints.

How do I find out what is refencing my P constraint?

Thanks,

Ken.
Re: Refrential Integrity Query [message #241742 is a reply to message #241739] Wed, 30 May 2007 09:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a join of dba_constraints on itself.
Your pk is in the R_% columns.

Regards
Michel
Re: Refrential Integrity Query [message #241749 is a reply to message #241730] Wed, 30 May 2007 09:45 Go to previous messageGo to next message
kdipankar
Messages: 9
Registered: December 2006
Junior Member
select b.table_name                  "Table Name"
      ,nvl(d.table_name,'NA')        "Referenced Table Name"
      ,a.column_name                 "Column Name"
	  ,nvl(c.column_name,'NA')        "Referenced Column Name"
	  ,b.constraint_name             "Constraint Name"           
	  ,nvl(b.r_constraint_name,'NA') "Referenced Constraint Name"
	  ,decode(b.constraint_type,'P','Primary Key','C','Check Constraint','U','Unique Constraint','R','Referential Integrity') "Constraint Type" 
from   dba_cons_columns   a
      ,dba_constraints    b
	  ,dba_cons_columns   c
      ,dba_constraints    d	  
where  b.table_name        = a.table_name
and    b.owner             = a.owner
and    b.owner             = <Schema Name>
and    a.constraint_name   = b.constraint_name
and    b.r_constraint_name = d.constraint_name(+)
and    c.constraint_name(+)= d.constraint_name 
and    a.table_name        = <Table Name>
Re: Refrential Integrity Query [message #241750 is a reply to message #241730] Wed, 30 May 2007 09:45 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Michel,

Cannot get this right. Thanks for your help.

select TABLE_NAME,CONSTRAINT_TYPE,OWNER from dba_constraints
where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from dba_constraints
where CONSTRAINT_TYPE = 'P'
and  TABLE_NAME = '<TABLE_NAME>'
and OWNER = '<OWNER>')


Can you point out where I am going wrong here please?

TIA,

Ken.
Re: Refrential Integrity Query [message #241764 is a reply to message #241750] Wed, 30 May 2007 10:27 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Its working for me:

SQL> CREATE TABLE tab1 (a NUMBER PRIMARY KEY);

Table created.

SQL> CREATE TABLE tab2 (a NUMBER PRIMARY KEY, b NUMBER REFERENCES tab1(a));

Table created.

SQL>
SQL> SELECT table_name,constraint_type,owner FROM user_constraints
  2  WHERE r_constraint_name IN (SELECT constraint_name FROM user_constraints
  3          where CONSTRAINT_TYPE = 'P'
  4          and  TABLE_NAME = 'TAB1'
  5          and OWNER = 'SCOTT')
  6  /

TABLE_NAME                     C OWNER
------------------------------ - ------------------------------
TAB2                           R SCOTT
Re: Refrential Integrity Query [message #241783 is a reply to message #241750] Wed, 30 May 2007 10:58 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use dba views (and not user as Frank show you) you have to put the condition on (r_owner,r_constraint_name) not just r_constraint_name.

Regards
Michel
Previous Topic: Working with Arrays
Next Topic: how to know the time of creation of column
Goto Forum:
  


Current Time: Sat Dec 03 20:11:08 CST 2016

Total time taken to generate the page: 0.09672 seconds