G-1220

🆓

Warning

Avoid composite primary keys.

Rule Details

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

Reason

Composite primary keys can make data modeling and querying more complicated because relationships become harder to manage and maintain. They often involve multiple columns in join operations, leading to verbose queries and increased chance of errors or performance issues. Opting for a single-column surrogate key helps ensure a simpler, more consistent design that is easier to change or extend in the future.

Example

Non-Compliant Example

create table orders (
   order_no integer primary key, 
   other_order_attrs json
);

create table order_items (
   order_no integer,
   order_item_no integer,
   other_order_item_attrs json,
   constraint order_items_pk primary key (order_no, order_item_no),
   constraint order_items_orders_fk foreign key (order_no) references orders
);

Compliant Solution - ★★★★★

create table orders (
   order_no integer primary key, 
   other_order_attrs json
);

create table order_items (
   order_item_id integer primary key,
   order_no integer,
   order_item_no integer,
   other_order_item_attrs json,
   constraint order_items_uk1 unique (order_no, order_item_no),
   constraint order_items_orders_fk foreign key (order_no) references orders
);

Tests

Test SQL query

select obj.owner || '.' || obj.object_name as identifier,
       'Composite primary key on ' || lower(obj.object_type) || ' ' || obj.object_name
         || ' (' || listagg(cc.column_name, ', ') within group (order by cc.position) || ').' as message
  from dba_objects obj
  join dba_constraints c
    on c.owner = obj.owner
       and c.table_name = obj.object_name
  join dba_cons_columns cc
    on cc.constraint_name = c.constraint_name
   and cc.owner = c.owner
 where c.constraint_type = 'P'
   and obj.object_Name not like 'AQ$%'
   and obj.object_type in (#ObjectTypesWithPrimaryKey#)
   and obj.owner in (#SchemaNames#)
 group by obj.owner, obj.object_type, obj.object_name
having count(*) > 1
 order by obj.owner, obj.object_type, obj.object_name

Test results

Identifier	Message	Migration
DBL_OWNER.ORDER_ITEMS	Composite primary key on table ORDER_ITEMS (ORDER_NO, ORDER_ITEM_NO).	-

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 Composite Key