G-1230
πWarning
Avoid tables without relationships.
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