G-9208

🆓

Warning

Always follow naming conventions for assertions.

Rule Details

Severity Level: Minor
Quality Area: Maintainability
Fixing times: 1 mins
Local-easy
Database Support: OracleDB (v23.42.0 +)

Reason

All table constraints share the same namespace. Follow naming conventions to prevent naming conflicts, improve readability, and clearly indicate the scope without forcing the use of qualified names. A common practice is to use a prefix and/or suffix to distinguish the identifier types.

Recommendations

Explain the condition being tested. Describe the expected outcome. Include the involved table names.

Examples

department_has_employees_as
manager_of_department_has_employees_as

Example

Non-Compliant Example

create assertion department_has_employees check (
   exists (
      select 1
        from departments d
       where exists (
                select 1
                  from employees e
                 where e.department_id = d.department_id
             )
   )
   initially deferred
);

Issues

Line	Column	Message
1	18	Assertion department_has_employees does not match '(?i)^[a-z][a-z0-9$#_]*_as$'.

Compliant Solution - ★★★★★

create assertion department_has_employees_as check (
   exists (
      select 1
        from departments d
       where exists (
                select 1
                  from employees e
                 where e.department_id = d.department_id
             )
   )
   initially deferred
);

Tests

Test SQL query

select owner || '.' || object_name as identifier,
       'Assertion ' || object_name || ' does not match ''' ||
       lower(#AssertionPattern#) || '''.' as message
  from dba_objects
 where owner in (#SchemaNames#)
   and object_type = 'ASSERTION'
   and not regexp_like(object_name, replace(lower(#AssertionPattern#), '(?i)', null), 'i')
 order by identifier

Test results

Identifier	Message	Migration
DBL_OWNER.DEPARTMENT_HAS_EMPLOYEES	Assertion DEPARTMENT_HAS_EMPLOYEES does not match '(?i)^[a-z][a-z0-9$#_]*_as$'.	-

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
AssertionPattern	Regular expression pattern for SQL assertions.	(?i)^[a-z][a-z0-9$#_]*_as$
SchemaNames	Comma-separated list of database schemas owning the database objects of an application.	dbl_owner

References

related to Trivadis Database Object Naming Conventions
The naming conventions for database objects in general covering primary keys, foreign keys, unique and check constraints.
related to SQL:2023 - Create Assertion
The syntax of the CREATE ASSERTION statement as defined in SQL:2023 standard.
related to SQL Assertions - Oracle Forums
Discussion initiated by Toon Koppelaars on the implementation of the CREATE ASSERTION statement in the Oracle database. Discussion closed on 2 August 2024, indicating that an implementation is in progress.