Home » SQL & PL/SQL » SQL & PL/SQL » how to find what check conditions are given to which data field of table.
how to find what check conditions are given to which data field of table. [message #187175] Fri, 11 August 2006 03:57 Go to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi buddies

if i have created the table as follwos

sql>create table sen(name varchar2(20),sal number check(sal between 1000 and 8000));

so i have given check condition for sal data field.

in future how can i find that for which data fields, which check condition are given.

thanks a lot in advance.
Re: how to find what check conditions are given to which data field of table. [message #187176 is a reply to message #187175] Fri, 11 August 2006 04:01 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

user_constraints and user_cons_columns


regards,
Re: how to find what check conditions are given to which data field of table. [message #187189 is a reply to message #187175] Fri, 11 August 2006 04:34 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi dhananjay

i am new to oracle. so pls give the full command line for question.

i entered as like

sql>user_constraints and user_cons_columns;

and

sql>user_constraints;

and

sql>scott_constraints;

but it shows the error "unknown command beginning".

so pals pls give the correct command line not syntax.

or give command line and syntax. i am very newbie to sql.

i am eagarly expecting your reply.

thanks a lot in advance.
Re: how to find what check conditions are given to which data field of table. [message #187194 is a reply to message #187189] Fri, 11 August 2006 04:42 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

select * from user_cons_columns where table_name='EMP'
OR 
select * from user_cons_columns where table_name='EMP'

Re: how to find what check conditions are given to which data field of table. [message #187328 is a reply to message #187175] Sat, 12 August 2006 03:11 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi dhananjay

i entered as you told like


select * from user_cons_columns where table_name='EMP';

            or 

select * from user_cons_columns where table_name-'sen';

no rows are selected.



why it is not working? but the above two tables are avilable there with datas adn check condition.

pls help me.

pls put correct coding very urgent.

thanks a lot in advance.
Re: how to find what check conditions are given to which data field of table. [message #187334 is a reply to message #187328] Sat, 12 August 2006 04:40 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

 it's working for me.i think the table name 'SEN' should be in upper case.please check.


regards,
Re: how to find what check conditions are given to which data field of table. [message #187344 is a reply to message #187328] Sat, 12 August 2006 07:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are you connected as the owner of the table EMP?
Queries on user_cons_columns only show constraint-columns of tables you own. If you are not the owner, try all_cons_columns instead.
Re: how to find what check conditions are given to which data field of table. [message #187372 is a reply to message #187175] Sat, 12 August 2006 22:46 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi dhananjay/frank

thanks a lot.

it is working. yeah there was a problem in "sen". correct ans "SEN".
SQL> create table sen(name varchar2(20),sal number check(sal between 1000 and 5000));

Table created.

SQL> select * from user_cons_columns where table_name='SEN';

OWNER                          CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------ ------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------
 POSITION
---------
SCOTT                          SYS_C00589                     SEN
SAL



SQL> select * from all_cons_columns where table_name='SEN';

OWNER                          CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------ ------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------
 POSITION
---------
SCOTT                          SYS_C00589                     SEN
SAL



but it is not showing what check condition we gave for that sal data field.

so i want to see what check conditions are given. i gave the check "check(sal between 1000 and 5000)" for sal data field.

the above command line did not show this check condition for sal data field.
Re: how to find what check conditions are given to which data field of table. [message #187380 is a reply to message #187372] Sun, 13 August 2006 03:11 Go to previous message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You forgot to apply Dhananjay's suggestion and query "user_constraints".

SQL> select * from user_constraints where table_name = 'SEN';
Previous Topic: Existing state of package... (Merged)
Next Topic: help me to do this
Goto Forum:
  


Current Time: Mon Dec 05 15:04:39 CST 2016

Total time taken to generate the page: 0.09580 seconds