G-9206
🆓Warning
Always follow naming conventions for foreign key 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/abbreviation followed by referenced table name/abbreviation followed by a _fk
and an optional number suffix.
Examples
empl_dept_fk
sct_icmd_ic_fk1
Example
Non-Compliant Example
create table parent ( parent_id raw(16) default sys_guid() not null, -- ... constraint parent_pk primary key (parent_id) ); create table child ( -- ... parent_id raw(16) not null, constraint child_parent foreign key (parent_id) references parent );
Issues
Line | Column | Message |
---|---|---|
10 | 15 |
★★★★★
Compliant Solution -
create table parent ( parent_id raw(16) default sys_guid() not null, -- ... constraint parent_pk primary key (parent_id) ); create table child ( -- ... parent_id raw(16) not null, constraint child_parent_fk foreign key (parent_id) references parent );
Tests
Test SQL query
select owner || '.' || constraint_name as identifier, 'Foreign key constraint ' || constraint_name || ' on ' || table_name || ' does not match ''' || lower(#ForeignKeyConstraintPattern#) || '''.' as message from dba_constraints fk where owner in (#SchemaNames#) and constraint_type = 'R' and table_name not like 'AQ$%' and not regexp_like(constraint_name, replace(lower(#ForeignKeyConstraintPattern#), '(?i)', null), 'i') and not exists ( select 1 from dba_queue_tables qt where qt.owner = fk.owner and qt.queue_table = fk.table_name ) and not exists ( select 1 from dba_tables t where t.iot_type is not null and t.owner = fk.owner and t.table_name = fk.table_name ) order by identifier
Test results
Identifier | Message | Migration |
---|---|---|
DBL_OWNER.CHILD_PARENT | Foreign key constraint CHILD_PARENT on CHILD does not match '(?i)^[a-z][a-z0-9$#_]*_fk\d*$'. | - |
Parameters
Use parameters to customize the rule to your needs.
Parameter | Description | Default Value |
---|---|---|
ForeignKeyConstraintPattern | Regular expression pattern for SQL foreign key constraints. | (?i)^[a-z][a-z0-9$#_]*_fk\d*$ |
SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |