Database constraints are the cornerstone of data integrity. You can block duplicates with primary keys and unique constraints, prevent orphaned rows with foreign keys, and validate values within a row with check constraints.

But what if your rules cross table boundaries?

Consider a quiz application like Oracle Dev Gym. It might require that:

  • Answers can only be submitted while a question is live.
  • Every question must have at least one choice.
  • Only one choice can be true for single-choice questions.

These rules span two tables:

  • The user_answers.submitted_at values must be between questions.live_start_time and questions.live_end_time.
  • Every question_id must exist in both the questions and question_choices tables.
  • For single_choice questions, exactly one row in question_choices has is_correct is true.

Enforcing these rules in application code is messy and error-prone.

Oracle AI Database introduces a simple solution:

Assertions.

Starting in release 23.26.1, you can define cross-table constraints with the create assertion command. Here’s how you use assertions to implement rules like those above.

A timeline showing question live start-end dates and attempts to submit questions

Ensure answers fall within start-end dates

Imagine users can only answer questions while they are live. To enforce this in SQL, you must assert that:

Every answer is submitted between its question’s live start and end times.

In the past, you couldn’t express “every answer satisfies this condition” with SQL. You had to invert the logic to assert that:

There is not an answer that is not submitted between its question’s live start and end times.

You can run a query to verify this, like so:

Put this logic in an assertion to enforce the rule:

With this in place, the database will raise an exception whenever you submit an answer outside the question’s live times:

But double negatives are confusing. Wouldn’t you like to write the assertion to match the original rule?

Every answer is submitted between its question’s live start and end times

With Oracle AI Database’s new universal (all – satisfy) condition, you can write:

  • For all questions and answers,
  • Satisfy that the submitted time is between the question’s live start and end.

Which becomes an assertion like:

The all – satisfy syntax is a new feature in 23.26.1, available only within assertions.

Example SQL assertion to ensure every question has at least one choice.

Guarantee questions have at least one choice

Every question needs at least one answer option. To guarantee this, enforce the rule:

For every question, there must exist a question choice.

You can express the rule using this universal expression:

This assertion creates a circular dependency:

  • The foreign key requires a question to exist to add its choices.
  • The assertion requires the choices to exist to add a question.

This stops you from inserting any new questions!

To overcome this, the assertion is in the deferrable initially deferred state. This delays validation until commit time. This enables you to insert a new question and its choices in one transaction.

For example, this adds a question without any choices:

The insert succeeds, but the transaction fails. To save the question, add choices before committing:

This rule also applies to delete statements. If you remove all the choices for a question, the transaction will error on commit:

Only one choice can be true for single-choice questions

Questions may come in two forms:

  • Multiple-choice: any number of options may be correct
  • Single-choice: exactly one option is correct

This gives rise to the rule:

For every single-choice question, there must be only one true choice

You can enforce this rule by extending the previous assertion:

  • Filter out multiple-choice questions in the all clause.
  • Ensure there exists at least one true choice in the satisfy clause.
  • Use a nested not exists expression to confirm no other choices are also marked as true.

Leading to the following assertion:

This has two nested [not] exists expressions. This is the maximum allowed in a universal expression.

You could also rewrite it with three nested not exists expressions; again this is the most permitted.

Learn more

For a deeper dive into the details, watch the recording from the Ask TOM Live session where we showed assertions in action:

Summary

Assertions are a simple way to define cross-table integrity rules. Download Oracle AI Database Free and try them out today!

To run the examples in this post, create these tables: