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 '^[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
);

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
AssertionPattern	Case-insensitive regular expression pattern for SQL assertions.	^[a-z][a-z0-9$#_]*_as$

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 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.
related to SQL:2023 - Create Assertion
The syntax of the CREATE ASSERTION statement as defined in SQL:2023 standard.