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
   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;
   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 number_of_relevant_columns(o.owner, o.object_name) > 0
         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,
       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
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 Relational model