rules repository

G-9202

🆓
Warning

Always follow naming conventions for table/view columns.

Reason

SQL identifiers share the same namespace as PL/SQL identifiers. 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

Singular name of what is stored in the column (unless the column data type is a collection, in this case you use plural names).

Add a comment to the database dictionary for every column. See also G-1270.

Example

Non-Compliant Example

create table departements (
   numéro_de_département number
   -- ...
);
Issues
LineColumnMessage
24column numéro_de_département does not match '(?i)^[a-z0-9$#_]+'.

We do not want accented letters in column names.

Compliant Solution - ★★★★★

create table departements (
   numero_de_departement number
   -- ...
);

All accented letters are replaced with plain Latin letters.

Tests

Test SQL query

select owner || '.' || table_name || '.' || column_name as identifier,
       'Column ' || column_name || ' in ' || table_name || ' does not match '''
       || lower(#ColumnPattern#) || '''.' as message
  from dba_tab_columns c
 where owner in (#SchemaNames#)
   and not regexp_like(column_name, replace(lower(#ColumnPattern#), '(?i)', null), 'i')
   and not exists (
          select 1
           from dba_tables t
           where t.iot_type is not null
              and t.owner = c.owner
              and t.table_name = c.table_name
       )
   and not exists (
          select 1
            from dba_queue_tables t
           where t.owner = c.owner
             and t.queue_table = c.table_name
       )
   and table_name not like 'BIN$%'
 order by identifier

Test results

IdentifierMessageMigration
DBL_OWNER.DEPARTEMENTS.NUMÉRO_DE_DÉPARTEMENTColumn NUMÉRO_DE_DÉPARTEMENT in DEPARTEMENTS does not match '(?i)^[a-z][a-z0-9$#_]*$'.-

Parameters

Use parameters to customize the rule to your needs.

ParameterDescriptionDefault Value
ColumnPatternRegular expression pattern for SQL table/view column names.(?i)^[a-z][a-z0-9$#_]*$
SchemaNamesComma-separated list of database schemas owning the database objects of an application.dbl_owner

References