rules repository

G-9205

🆓
Warning

Always follow naming conventions for unique constraints.

Reason

All table constraints share the same namespace. 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

Table name or table abbreviation followed by the role of the unique constraint, a _uk and an optional number suffix.

Examples

  • employees_name_uk
  • departments_deptno_uk
  • sct_contracts_uk
  • sct_coli_uk
  • sct_icmd_uk1

Example

Non-Compliant Example

create table emp (
   -- ...
   ename varchar2(14 char) not null,
   -- ...
   constraint ename unique (ename)
);
Issues
LineColumnMessage
515Unique constraint ename does not match '(?i)^[a-z][a-z0-9$#_]*_uk\d*$'.

Compliant Solution - ★★★★★

create table emp (
   -- ...
   ename varchar2(14 char) not null,
   -- ...
   constraint emp_ename_uk unique (ename)
);

Tests

Test SQL query

select owner || '.' || constraint_name as identifier,
       'Unique constraint ' || constraint_name || ' on ' || table_name || ' does not match '''
       || lower(#UniqueConstraintPattern#) || '''.' as message
  from dba_constraints uk
 where owner in (#SchemaNames#)
   and constraint_type = 'U'
   and table_name not like 'AQ$%'
   and not regexp_like(constraint_name, replace(lower(#UniqueConstraintPattern#), '(?i)', null), 'i')
   and not exists (
          select 1
            from dba_queue_tables qt
           where qt.owner = uk.owner
             and qt.queue_table = uk.table_name
       )
   and not exists (
          select 1
            from dba_tables t
           where t.iot_type is not null
             and t.owner = uk.owner
             and t.table_name = uk.table_name
       )
 order by identifier

Test results

IdentifierMessageMigration
DBL_OWNER.ENAMEUnique constraint ENAME on EMP does not match '(?i)^[a-z][a-z0-9$#_]*_uk\d*$'.-

Parameters

Use parameters to customize the rule to your needs.

ParameterDescriptionDefault Value
UniqueConstraintPatternRegular expression pattern for SQL unique constraints.(?i)^[a-z][a-z0-9$#_]*_uk\d*$
SchemaNamesComma-separated list of database schemas owning the database objects of an application.dbl_owner

References