Home » SQL & PL/SQL » SQL & PL/SQL » constraints
constraints [message #264539] Mon, 03 September 2007 12:08 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

I've created two tables T1 and T2 like,

SQL> create table T1(name varchar2(10),no number(4),
constraint pk primary key(no));

and

SQL> create table T2(sname varchar2(10),rno number(4),
constraint fk foreign key(rno)
references emp(no));

Now is there any data dictionary view which enables me to see on which table I've created the Foreign key.

I've tried :

user_constraints;
user_cons_columns;


please let me know.

Regards,

Raj
Re: constraints [message #264544 is a reply to message #264539] Mon, 03 September 2007 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've tried :

user_constraints;
user_cons_columns;

This is correct.

Regards
Michel
Re: constraints [message #264595 is a reply to message #264544] Mon, 03 September 2007 22:09 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello Sir,

But as I told you, I want to see which is the view to see on which table I've the Foreign Key. I'm unable to find using the user_constraints and user_cons_columns views.

So can you please help me out.

Regards,

Raj
Re: constraints [message #264597 is a reply to message #264539] Mon, 03 September 2007 22:18 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SQL> desc user_constraints
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
 R_OWNER                                            VARCHAR2(30)
 R_CONSTRAINT_NAME                                  VARCHAR2(30)
 DELETE_RULE                                        VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 DEFERRABLE                                         VARCHAR2(14)
 DEFERRED                                           VARCHAR2(9)
 VALIDATED                                          VARCHAR2(13)
 GENERATED                                          VARCHAR2(14)
 BAD                                                VARCHAR2(3)
 RELY                                               VARCHAR2(4)
 LAST_CHANGE                                        DATE
 INDEX_OWNER                                        VARCHAR2(30)
 INDEX_NAME                                         VARCHAR2(30)
 INVALID                                            VARCHAR2(7)
 VIEW_RELATED                                       VARCHAR2(14)

SQL> desc user_cons_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(4000)
 POSITION                                           NUMBER

SQL> 


Show us using CUT & PASTE plus <code-tags> what you have tried.
Re: constraints [message #264603 is a reply to message #264539] Mon, 03 September 2007 22:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
is there any data dictionary view which enables me to see on which table I've created the Foreign key.

I've tried :

user_constraints;
user_cons_columns;


I also used the same.


SQL> create table T1(name varchar2(10),no number(4),
constraint pk primary key(no));
  2

Table created.

SQL> SQL> create table T2(sname varchar2(10),rno number(4),
constraint fk foreign key(rno)
references emp(no));  2    3
references emp(no))
           *
ERROR at line 3:
ORA-00942: table or view does not exist


SQL> create table T2(sname varchar2(10),rno number(4),
  2   constraint fk foreign key(rno)
  3   references T1(no));

Table created.


SQL> select CONSTRAINT_NAME,R_CONSTRAINT_NAME , table_name  from user_constraints where table_name='T2';

CONSTRAINT_NAME                R_CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
FK                             PK
T2


SQL> select table_name from user_constraints where CONSTRAINT_NAME='PK';

TABLE_NAME
------------------------------
T1


Re: constraints [message #265076 is a reply to message #264539] Wed, 05 September 2007 06:23 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select a.table_name, a.constraint_name,c.column_name, b.table_name ref_table, d.column_name ref_col_name 
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d   
where 
b.constraint_name = d.constraint_name
and a.constraint_name = c.constraint_name
and a.r_constraint_name = b.constraint_name
and a.constraint_type = 'R'
and a.table_name = 'T2'

Previous Topic: firstname and middel name in capitals?
Next Topic: Exchange partition
Goto Forum:
  


Current Time: Sun Dec 04 14:44:27 CST 2016

Total time taken to generate the page: 0.09147 seconds