rules repository

G-9203

🆓
Warning

Always follow naming conventions for indexes.

Reason

Index names share the same namespace as tables, indexes, views, etc. Follow naming conventions to prevent naming conflicts, improve readability, and clearly indicate the scope without forcing the use of qualified names. A common practice is to use a prefix and/or suffix to distinguish the identifier types.

Recommendations

Indexes serving a constraint (primary, unique or foreign key) are named accordingly.

Other indexes should have the name of the table and columns (or their purpose) in their name and should also have _idx as a suffix.

Example

Non-Compliant Example

create index employees_first_name on employees(first_name);
Issues
LineColumnMessage
114index employees_first_name does not match '(?i)^[a-z][a-z0-9$#_]*_(pk|uk\d*|fk\d*|idx)$'.

Compliant Solution - ★★★★★

create index employees_first_name_idx on employees(first_name);

Tests

Test SQL query

select owner || '.' || index_name as identifier,
       'Index ' || index_name || ' on ' || table_name || ' does not match '''
       || lower(#IndexPattern#) || '''.' as message
  from dba_indexes i
 where owner in (#SchemaNames#)
   and table_name not like 'AQ$%'
   and index_type != 'LOB'
   and not regexp_like(index_name, replace(lower(#IndexPattern#), '(?i)', null), 'i')
   and not exists (
          select 1
            from dba_queue_tables qt
           where qt.owner = i.owner
             and qt.queue_table = i.table_name
       )
   and not exists (
          select 1
            from dba_tables t
           where t.iot_type is not null
             and t.owner = i.owner
             and t.table_name = i.table_name
       )
   and index_name not like 'BIN$%'
 order by identifier

Test results

IdentifierMessageMigration
HR.EMPLOYEES_FIRST_NAMEIndex EMPLOYEES_FIRST_NAME on EMPLOYEES does not match '(?i)^[a-z][a-z0-9$#_]*_(pk|uk\d*|fk\d*|idx)$'.-

Parameters

Use parameters to customize the rule to your needs.

ParameterDescriptionDefault Value
IndexPatternRegular expression pattern for SQL indexes.(?i)^[a-z][a-z0-9$#_]*_(pk|uk\d*|fk\d*|idx)$
SchemaNamesComma-separated list of database schemas owning the database objects of an application.dbl_owner

References