G-9207
🆓Warning
Always follow naming conventions for check 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 column and/or role of the check constraint, a _ck
and an optional number suffix.
Examples
employees_salary_min_ck
orders_mode_ck
Example
Non-Compliant Example
create table emp ( -- ... sal number(7,2), constraint emp_sal check (sal > 0) );
Issues
Line | Column | Message |
---|---|---|
4 | 15 |
★★★★★
Compliant Solution -
create table emp ( -- ... sal number(7,2), constraint emp_sal_gt_zero_ck check (sal > 0) );
Tests
Test SQL query
select owner || '.' || constraint_name as identifier, 'Check constraint ' || constraint_name || ' on ' || table_name || ' does not match ''' || lower(#CheckConstraintPattern#) || '''.' as message from dba_constraints ck where owner in (#SchemaNames#) and constraint_type = 'C' and table_name not like 'AQ$%' and constraint_name not like 'SYS\_C%' escape '\' and not regexp_like(constraint_name, replace(lower(#CheckConstraintPattern#), '(?i)', null), 'i') and not exists ( select 1 from dba_queue_tables qt where qt.owner = ck.owner and qt.queue_table = ck.table_name ) and not exists ( select 1 from dba_tables t where t.iot_type is not null and t.owner = ck.owner and t.table_name = ck.table_name ) order by identifier
Test results
Identifier | Message | Migration |
---|---|---|
DBL_OWNER.EMP_SAL | Check constraint EMP_SAL on EMP does not match '(?i)^[a-z][a-z0-9$#_]*_ck\d*$'. | - |
Parameters
Use parameters to customize the rule to your needs.
Parameter | Description | Default Value |
---|---|---|
SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |
CheckConstraintPattern | Regular expression pattern for SQL check constraints. | (?i)^[a-z][a-z0-9$#_]*_ck$ |