G-1210

🆓

Error

Never create a table without a primary key.

Rule Details

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

Reason

Defining a primary key for each table is essential for maintaining data integrity and ensuring efficient data management. An enabled primary key guarantees that each record can be uniquely identified, facilitates reliable relationships between tables, prevents duplicate entries and improves query performance for primary key access and DML of child tables with enabled foreign key constraints.

An exception to this rule is tables in an intermediate schema used for data cleansing, where processing of inconsistent data is required.

There are cases where you do not want to enable a primary key to avoid a global unique index. For example in a partitioned fact table where the partition key is not part of the primary key. In such cases, you can still define a primary key, but disable it. A disabled primary key has no impact on performance, but is a great help for documentation and understanding the data model.

Documentation is also the reason why it may make sense to define primary keys on views.

Example

Non-Compliant Example

create table t (
   id   integer           not null,
   name varchar2(20 char) not null
);

Compliant Solution - ★★★★★

create table t (
   id   integer           not null primary key,
   name varchar2(20 char) not null
);

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;
   pk as (
      select owner, table_name
        from dba_constraints
       where constraint_type = 'P'
         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) > 0
         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 primary key on ' || lower(obj.object_type) || ' ' || obj.object_name || '.' as message
  from obj
  left join pk
    on pk.owner = obj.owner
       and pk.table_name = obj.object_name
 where pk.owner is null
order by obj.owner, obj.object_type, obj.object_name

Test results

Identifier	Message	Migration
DBL_OWNER.T	Missing primary key on table T.	-

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 Primary Key
similar to plsql:S1614
The scope of plsql:S1614 is the create table statement. It enforces you to define a primary key as part of the create table statement.