Home » SQL & PL/SQL » SQL & PL/SQL » How to view the status of the constraints in a single table (Oracle 9i. HP-ux)
How to view the status of the constraints in a single table [message #351124] Mon, 29 September 2008 07:01 Go to next message
sanei05
Messages: 104
Registered: September 2008
Senior Member
How to view the status of the constraints in a single table. Can I have the query...

I have a table which has a primary key constraint.I want to know whether the constraint is enabled or disabled in a particular table.

Re: How to view the status of the constraints in a single table [message #351127 is a reply to message #351124] Mon, 29 September 2008 07:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
USER_CONSTRAINTS

Regards,
Rajat
Re: How to view the status of the constraints in a single table [message #351128 is a reply to message #351124] Mon, 29 September 2008 07:18 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Query user/all/dba_constraints
Re: How to view the status of the constraints in a single table [message #351130 is a reply to message #351124] Mon, 29 September 2008 07:29 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Here is a query:

set pagesize 1000 linsize 450
column table_name format a35
column constraint_name format a35
column column_name format a35

break on table_name skip 2

select 
   a.table_name, 
   a.constraint_name, 
   b.column_name
   a.status
from   USER_CONSTRAINTS a, USER_CONS_COLUMNS b
where  
   a.table_name = b.table_name and
   a.constraint_name = b.constraint_name and
   a.table_name = '<Your Table Here>'
order by 
   a.table_name,
   a.constraint_name
/


Hope this helps.

David
Re: How to view the status of the constraints in a single table [message #351145 is a reply to message #351130] Mon, 29 September 2008 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query is wrong.
OP asks for status, he does not care about columns.

Regards
Michel
Re: How to view the status of the constraints in a single table [message #351152 is a reply to message #351145] Mon, 29 September 2008 09:29 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Michel to be fair here the query is NOT wrong. It just contains more info than the OP asked for. It still gives the status of the constraint. The OP asked for a query so I provided one. He can make adjustments as desired.

Regards,

David
Re: How to view the status of the constraints in a single table [message #351155 is a reply to message #351152] Mon, 29 September 2008 09:32 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
OP also asked for Primary keys only.
Your query lists a record for each column present in any constraint on the table.

I would hardly call that a correct query, considering the requirements.
Re: How to view the status of the constraints in a single table [message #351157 is a reply to message #351155] Mon, 29 September 2008 09:41 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Frank, the OP also asked for a query which you failed to provide. That is also part of the requirements.

Regards,

David
Re: How to view the status of the constraints in a single table [message #351162 is a reply to message #351152] Mon, 29 September 2008 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dkranes wrote on Mon, 29 September 2008 16:29
Michel to be fair here the query is NOT wrong. It just contains more info than the OP asked for.

Why don't you also add all indexes, tablespaces with their free space and allocation map?
The question is simple, it just requires a single view with 2 output columns, one for the constraint name and one for the status.

Regards
Michel

[Updated on: Mon, 29 September 2008 09:55]

Report message to a moderator

Re: How to view the status of the constraints in a single table [message #351164 is a reply to message #351157] Mon, 29 September 2008 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dkranes wrote on Mon, 29 September 2008 16:41
Frank, the OP also asked for a query which you failed to provide

But if you read the forum you will see that we prefer to give clue to help people learn how to do things instead of a whole solution (and even less an appropriate solution.

Regards
Michel

Re: How to view the status of the constraints in a single table [message #351165 is a reply to message #351157] Mon, 29 September 2008 09:59 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
dkranes wrote on Mon, 29 September 2008 16:41
Frank, the OP also asked for a query which you failed to provide. That is also part of the requirements.

Regards,

David

It's custom here at OraFAQ to not provide full-blown solutions for simple problems. We think we can help people more by pointing them in the right direction, then to give them the solution.
The "give a man a fish..." approach.
Re: How to view the status of the constraints in a single table [message #351166 is a reply to message #351162] Mon, 29 September 2008 10:00 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Michel, point taken. I . Thank you and have a great day! Razz

David
Re: How to view the status of the constraints in a single table [message #351167 is a reply to message #351165] Mon, 29 September 2008 10:03 Go to previous messageGo to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Frank I believe that is a good approach and will be sure to consider that in further correspondences. Thank you.

David Smile
Re: How to view the status of the constraints in a single table [message #351168 is a reply to message #351167] Mon, 29 September 2008 10:07 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Cool!

Nice to welcome yet another contributing member!
Re: How to view the status of the constraints in a single table [message #351266 is a reply to message #351168] Tue, 30 September 2008 02:41 Go to previous message
sanei05
Messages: 104
Registered: September 2008
Senior Member
Guys,
thanks a lot.
its working..
Previous Topic: Handling single quotes in parameter passing
Next Topic: Use Partition Name in select for remote database
Goto Forum:
  


Current Time: Mon Nov 04 04:48:47 CST 2024