Last month we launched a content bounty to celebrate the addition of assertions in Oracle AI Database 23.26.1. We invited Oracle ACEs to share real-world use cases.

A SQL statement to find the assertion bounty winners select winners from assertions where creator = 'Oracle ACE' order by real_world_applicability + complexity + novelty desc fetch first 3 rows only

The top three submissions are, from third to first:

Third place: Assertions for data integrity on a less-than-ideal data model – Kim Berg Hansen

Kim took us on a tour of using assertions to solve challenges from his previous job: conditional foreign keys. He demonstrated two scenarios:

  • Child rows can only reference a subset of parent rows.
  • Child rows reference different parent tables based on their type.

This clearly demonstrated a real-world problem Kim had encountered. While conditional foreign keys are a sign of a poor data model, this is a common challenge when working with legacy applications.

Second place: Temporal Assertions – Anthony Harper

Anthony submitted a trio of posts showing how to check customer discounts match their loyalty level. He showed when you change a customer’s status, there is a mismatch between their current level and the discounts for previous orders. So you need take time into account when validating values.

Keeping data consistent as it changes over time is a long-standing challenge. Anthony shows how you can do this with assertions using simple SQL.

First place: Enforcing Acyclic Hierarchies With Assertions – Philipp Salvisberg

Philipp showed a common problem: if you’re traversing a tree with a loop leads to errors. You can avoid this with the nocycle clause of connect by or cycle clause for recursive with, but it’s better to stop invalid data in the first place. Sadly, these features are both unsupported by assertions.

Ingeniously, Philipp showed how you can use assertions to ensure a maximum tree depth N: join the table to itself N+1 times. If this returns a row, you have a loop. This gives simple loop detection, provided you can limit the levels in your hierarchy.

It was a close call between Anthony and Philipp for first place. What nudged Philipp to the top spot is he submitted two separate use cases; his second post on temporal data validation also merited a top ranking!

Thank you to all the ACEs who entered: this shows there’s clear demand for data integrity rules in the database. Download Oracle AI Database Free to try assertions.

Finally, special thanks to Toon Koppelaars for helping to judge the entries.