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
Line | Column | Message |
---|---|---|
2 | 4 |
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
Identifier | Message | Migration |
---|---|---|
DBL_OWNER.DEPARTEMENTS.NUMÉRO_DE_DÉPARTEMENT | Column 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.
Parameter | Description | Default Value |
---|---|---|
ColumnPattern | Regular expression pattern for SQL table/view column names. | (?i)^[a-z][a-z0-9$#_]*$ |
SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |