Home » RDBMS Server » Performance Tuning » How to see the constraints in the table ?
icon5.gif  How to see the constraints in the table ? [message #214094] Sun, 14 January 2007 23:22 Go to next message
victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore
Member

Hi,

I am try to see the table discription in EMP

SQL> disc emp;


Name Null? Type
----------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
----------------------------------------

I want to see wheather EMPNO is constraints is NOT NULL OR PRIMARY KEY.WE ENTER DUPLICATE values mean we find out easily.
But i need to find using qury???



Re: How to see the constraints in the table ? [message #214095 is a reply to message #214094] Sun, 14 January 2007 23:27 Go to previous messageGo to next message
victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore
Member

we use
select * from ALL_constraints; --or
select * from all_cons_coumns;


we find out only ower,table name,column name,constraint name
.but we are not properly mention constraint name mean we are not find out particular constraint......i need constraint also......


by vetri
Re: How to see the constraints in the table ? [message #214096 is a reply to message #214094] Sun, 14 January 2007 23:28 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
check in "user_constraints" view.

SQL> select constraint_name,constraint_type,table_name
  2  from user_constraints
  3  where table_name = '&table_name';

SQL> select constraint_name, constraint_type,table_name
  2  from user_constraints
  3  where table_name = 'TEST';

CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - ------------------------------
PK_ID                          P TEST
UK_PHONE                       U TEST
CK_SALARY                      C TEST
SYS_C005016                    C TEST
FK_NO                          R TEST

Constraint type
Primary key >>> P
Foreign key >>> R
Check       >>> C
Unique      >>> U
Not null    >>> C ( with system generate name)



regards
Taj

[Updated on: Sun, 14 January 2007 23:41]

Report message to a moderator

Re: How to see the constraints in the table ? [message #215348 is a reply to message #214096] Sun, 21 January 2007 13:02 Go to previous message
cbruhn2
Messages: 41
Registered: January 2007
Member
From oracle 9i and onwards you could use dbms_metadata.get_ddl.
This will give you the definition of the table with constraints. But not the names of the constraints .
21:08:08 ORCL> set pages 0
21:12:29 ORCL> set long 99999 -- it could be a large table :-)
21:13:06 ORCL> select dbms_metadata.get_ddl('TABLE','D') from dual;
 
  CREATE TABLE "CRB"."D"
   (    "NAVN" VARCHAR2(20) NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 
 
 
Elapsed: 00:00:00.96
21:13:34 ORCL>


best regards
Previous Topic: Selecting from a big table.
Next Topic: SQL performance tuning
Goto Forum:
  


Current Time: Wed Nov 27 03:30:21 CST 2024