 How to see the constraints in the table ? [message #214094] |
Sun, 14 January 2007 23:22  |
|
|
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  |
|
|
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
