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
   uk as (
      select owner, table_name
        from dba_constraints
       where constraint_type = 'U'
         and owner in (#SchemaNames#) -- config parameter
   ),
   obj as (
      select owner, object_type, object_name
        from dba_objects o
       where object_type in (#ObjectTypesWithPrimaryKey#)
         and owner in (#SchemaNames#) -- config parameter
         and object_name not like 'AQ$%'
         and not exists (
                select 1
                  from dba_tables t
                 where t.iot_type is not null
                   and t.owner = o.owner
                   and t.table_name = o.object_name
             )
   )
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