G-1230

🆓

Warning

Avoid tables without relationships.

Rule Details

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

Reason

Tables that exist entirely on their own can undermine the relational structure and lead to potential data redundancy or inconsistency. Ensuring that every table has at least one relevant connection to another table helps maintain consistency, facilitate queries, and keep the database design cohesive.

One valid exception could be a table specifically designed to hold standalone or ephemeral data — such as system logs — that doesn’t require relational ties for integrity. This exception acknowledges that some data needs to remain independent for auditing, logging, or similar specialized uses.

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)
);

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)
);

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)
);

Tests

Test SQL query

with
   fk as (
      select r.owner, r.table_name, p.owner as r_owner, p.table_name as r_table_name
        from dba_constraints r
        join dba_constraints p
          on p.owner = r.r_owner
             and p.constraint_name = r.r_constraint_name
             and p.constraint_type = 'P'
       where r.constraint_type = 'R'
         and r.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 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.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,
       initcap(obj.object_type) || ' ' || obj.object_name || ' is neither source nor target of a relationship' as message
  from obj
  left join fk from_fk
    on from_fk.owner = obj.owner
       and from_fk.table_name = obj.object_name
  left join fk to_fk
    on to_fk.r_owner = obj.owner
       and to_fk.r_table_name = obj.object_name
 where from_fk.owner is null
   and to_fk.r_owner is null
 order by obj.owner, obj.object_type, obj.object_name

Test results

Identifier	Message	Migration
DBL_OWNER.CHILD	Table CHILD is neither source nor target of a relationship.	-
DBL_OWNER.PARENT	Table PARENT is neither source nor target of a relationship.	-

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
ObjectTypesWithPrimaryKey	Comma-separated list of object types that should be defined with a primary key.	table

References

related to Wikipedia Relational model