G-1250

🆓

Warning

Try to define a business key for each table.

Rule Details

Severity Level: Major
Quality Area: Reliability
Fixing times: 60 mins
Other
Database Support: OracleDB (v12.2.0 +)

Reason

When you rely on surrogate keys as the primary key, it’s critical to also define a business key to preserve real-world context and uniqueness. Surrogate keys ensure a simple, uniform means of identification, but they offer no intrinsic meaning or domain-specific checks. A well-defined business key provides these necessary domain constraints and clarity, making it easier to ensure data integrity and communicate about records both technically and with stakeholders.

Example

Non-Compliant Example

create table parent (
   parent_id   raw(16) default sys_guid() not null,
   parent_name varchar2(100 char)         not null,
   constraint parent_pk primary key (parent_id)
);

create table child (
   child_id   raw(16) default sys_guid() not null,
   parent_id  raw(16)                    not null,
   child_name varchar2(100 char)         not null,
   constraint child_pk primary key (child_id),
   constraint child_parent_fk foreign key (parent_id) references parent(parent_id)
);

Compliant Solution - ★★★★★

create table parent (
   parent_id   raw(16) default sys_guid() not null,
   parent_name varchar2(100 char)         not null,
   constraint parent_pk primary key (parent_id),
   constraint parent_uk1 unique (parent_name)
);

create table child (
   child_id   raw(16) default sys_guid() not null,
   parent_id  raw(16)                    not null,
   child_name varchar2(100 char)         not null,
   constraint child_pk primary key (child_id),
   constraint child_uk1 unique (parent_id, child_name),
   constraint child_parent_fk foreign key (parent_id) references parent(parent_id)
);

Tests

Test SQL query

with
   function number_of_relevant_columns( -- NOSONAR G-7460: nondeterministic function
      in_owner       in varchar2,
      in_object_name in varchar2
   ) return number is
      l_number_of_relevant_columns integer := 0;
   begin
     select count(*)
       into l_number_of_relevant_columns
       from dba_tab_cols
      where owner = in_owner
        and table_name = in_object_name
        and hidden_column = 'NO'
        and virtual_column = 'NO'
        and column_name not in ('RESID', 'ETAG')
        and data_type not in ('XMLTYPE', 'JSON', 'BFILE', 'ROWID', 'UROWID', 'BLOB', 'CLOB', 'NCLOB');
     return l_number_of_relevant_columns;
   end number_of_relevant_columns;
   uk as (
      select owner, table_name
        from dba_constraints
       where constraint_type = 'U'
         and owner in (#SchemaNames#)
   ),
   obj as (
      select owner, object_type, object_name
        from dba_objects o
       where object_type in (#ObjectTypesWithPrimaryKey#)
         and owner in (#SchemaNames#)
         and object_name not like 'AQ$%'
         and number_of_relevant_columns(o.owner, o.object_name) > 1 -- we need another column for a business key
         and not exists (
                select 1
                  from dba_queue_tables t
                 where t.owner = o.owner
                   and t.queue_table = o.object_name
             )
         and not exists (
                select 1
                  from dba_tables t
                 where t.owner = o.owner
                   and t.table_name = o.object_name
                   and (t.iot_type is not null or t.temporary = 'Y')
             )
   )
select obj.owner || '.' || obj.object_name as identifier,
       'Missing business key on ' || lower(obj.object_type) || ' ' || obj.object_name as message
  from obj
  left join uk
    on uk.owner = obj.owner
       and uk.table_name = obj.object_name
 where uk.owner is null
order by obj.owner, obj.object_type, obj.object_name

Test results

Identifier	Message	Migration
DBL_OWNER.PARENT	Missing business key on table PARENT.	-
DBL_OWNER.CHILD	Missing business key on table CHILD.	-

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
ObjectTypesWithPrimaryKey	Comma-separated list of object types that should be defined with a primary key.	table
SchemaNames	Comma-separated list of database schemas owning the database objects of an application.	dbl_owner

References

related to Wikipedia Natural key