Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle naming standards tips

Oracle Tips by Burleson Consulting

This document describes a standard naming convention for Oracle schema objects including table naming standards, index naming standards, constraint naming standards and column naming standards.

Steve Feuerstein has these  notes on PL/SQL naming conventions.

Oracle DBA's must pay careful attention to the structure and naming conventions and naming standards within the database.  All applications will reside within the same schema owner and naming conventions will be used to identify table/index components:

Oracle Schema Naming Standards

The following standards will be used in all schemas:

  • Schema objects - All non-table schema objects will be prefixed by their type and all index names will begin with idx, and all constraint names will begin with cons.
     
  • Referential Integrity conventions - All tables will have full RI, including PK constraints, FK constraints and check constraints. The default for foreign key constraints will be "On Delete Restrict", unless otherwise specified.  This means that no parent record can be deleted if there are corresponding child records.
     
  • Primary keys - Oracle Sequences will be used to generate unique row identifiers and all sequence numbers generally will start at one and increment by one.  
     
  • Check Constraints - Lists of valid values will be used in all cases to restrict column values and validity

Oracle table naming Standards

To simplify development, we will follow these rules that allow the developer to quickly identify each metadata object, with complete descriptive names:

  • Table Standards
     
    • All table names will be plural (e.g. users vs. user).
    • Full table names will be used whenever possible.
    • If a table name should exceed 30 characters, reduce the size of the table name in this order:
      • From the left of the table name, remove vowels from each word in the table name except for the first vowel of each word.
      •  If the table name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.
         

Oracle column naming Standards

  • Column Naming Standards
    • Column names should be spelled out whenever possible.
    • If a column name should exceed 30 characters, reduce the size of the column name in this order:
      • From the left of the column name, remove vowels from each word in the table name except for the first vowel of each word.
      •  If the column name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.

Oracle index naming Standards

  • Index Standards
    • Index names should follow this standard:

IDX_ttttt_nn

Where
IDX = Index
tttt = Table name the index is built on
nn = Numeric value that makes each table index unique.

    • If an index name should exceed 30 characters, reduce the size of the index name in this order:
      • From the left of the index name, remove vowels from each word in the table name except for the first vowel of each word.
      •  If the index name is still greater than 30 characters, use standardized shorthand indicators. Record this standard for consistent use.

Oracle constraints naming Standards

  • Constraint Standards
    • Primary key constraints will follow this naming convention:
      • PK_nnnnn
        Where nnnn = The table name that the index is built on.

         
      • UK_nnnnn_nn
        Where nnnn = The table name that the index is built on.
                        nn =  A number that makes the constraint unique.

         
      • FK_pppp_cccc_nn
        Where pppp = The parent table name
                    cccc = The child parent table name
                        nn = A number that makes the constraint unique

         

Sample names might include:

  • tables names - persons, islands, dsm_iv_codes

     
  • table column names - first_name, dsm_iv_code_description

     
  • constraint names - pk_ehd_food_establishment, fk_ehd_food_establishment_01

     
  • index names - idx_ssd_dsm_01

 

Oracle application naming Standards

Application Prefixes - All table/index/column/constraint names will use standard prefixes.  Each application area will be identified with a three-character abbreviation, and this abbreviation will be used for the names of all tables, indexes and constraints.  We will not use system-generated constraint or index names.  For example, assume we have these two application areas:

  • General cross-area objects = GEN
  • Social Services Department = SSD
  • Health Services Department = HSD

Object names - To simplify development, we will follow these standards that allow the developer to quickly identify each metadata object, with complete descriptive names:

  • The application prefix will be used in all metadata entries, including tables, indexes, constraints and table columns.
     
  • The table name will be included in all index, constraint and table column names.
     
  • The type of constraint will be specified in all constraint names, using the abbreviations PK, FK and CHECK

Oracle table codes naming Standards

Whenever possible, “codes” will not be used, and the actual data values will be stored in the tables. For example, we will use full-values for all standardized data columns:

alter table
   ehd_food_establishment
add constraint
   ehd_food_establishment_type_check
check
  ehd_establishment_type
in (
   ‘Restaurant’,
   ‘Hotels’,
   ‘Itinerant vendor’,
   ‘Food shop’,
   ‘Grocery store’,
   ‘Supermarket’,
   ‘Cook shop’,
   ‘bar’
);


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.