Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Finding tables without Primary Keys

John_75Jun 22 2010 — edited Jun 22 2010
DB version : 10g Release 2

We have several tables that don't have a Primary Key !

I need to use USER_CONSTRAINTS view to find which of those tables that don't have a primary key.
The following query won't work as it plainly returns all records where CONSTRAINT_TYPE != 'P'
select table_name from user_Constraints WHERE constraint_type != 'P'
I need to skip those table_names which have at least one occurence of CONSTRAINT_TYPE = 'P'.
This post has been answered by Nimish Garg on Jun 22 2010
Jump to Answer

Comments

Prazy
This?
select table_name from user_tables a where 
		not exists (
				select null from user_constraints where constraint_type ='P' and table_name = a.table_name
			   )
NOT tested..

Regards,
Prazy
770225
Try
select distinct table_name, constraint_name, contraint_type from user_Constraints WHERE constraint_type != 'P'
Edited by: feersum_endjinn on 22-Jun-2010 04:25
Nimish Garg
Answer
select table_name from user_tables a where table_name
not in
(
select table_name from user_constraints where constraint_type ='P'
)
Marked as Answer by John_75 · Sep 27 2020
John_75
Nimish, Prazy
Both of your solutions worked. Thank you
Sven W.
If you use SQL Developer then one of the pre defined reports gives this information as well.

Look at the reports tab and then: All Reports>Data Dictionary Reports>Tables>Quality>Tables without primary key

Another very useful quality check you can find there is "Tables with non indexed foreign keys"

To see the select behind the report you can copy it and create it as user defined report. Then check the Select or the XML.
Prazy
Sven W. wrote:
If you use SQL Developer then one of the pre defined reports gives this information as well.

Look at the reports tab and then: All Reports>Data Dictionary Reports>Tables>Quality>Tables without primary key
That's quite nice info. I haven't used SQL Developer much as I find SQL*Plus screen to be more classic ;). Anyways, will look around SQL Dev from now.
Gaff
Just for completeness I'll throw in that if you have Toad you can run something called "DB Health Check" and get a nice report that has, among other things:
 Alert.log Access Error    
 Redo Log Group Sizes and Quantities   
 Unanalyzed System Tables - 10g    
 Unanalyzed System Indexes - 10g  
 DB Link Connectivity    
 Users (besides SYS) with SYSTEM as a default tablespace    
 Tablespaces with less than 10% free space    
 Location of SYS.AUD$    
 Tables with no primary key   
 Tables with no unique key or index   
 Tables with > 6 indexes    
 Tables with > 100 columns  
 Tables with LONG or LONG RAW datatypes    
 Tables with row size > tablespace block size    
 Redundant Indexes (same leading columns)    
 Primary and unique keys using non-unique indexes    
 FK Constraints with nonmatching column definitions    
 FK's w/o Matching Indexes (or an unusable one)   
 Unique keys with nullable columns    
 Jobs    
 Objects with grants but no corresponding synonyms    
 Invalid Objects   
 Disabled Constraints   
 Disabled Triggers
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 20 2010
Added on Jun 22 2010
7 comments
11,083 views