Oracle 26ai: Easy Data Validation with Assertions

What You Will Learn

Discover the new Assertion feature in Oracle 26ai. You will learn what it is, why it helps, and how to use it in real-world scenarios. 


The Problem with Check Constraints

Usually, we use a CHECK constraint to restrict what data goes into a column. But this method is too rigid.

For example, imagine a table with a STATUS column that only allows 'ACTIVE' or 'INACTIVE'. If you later decide to add a new status like 'DELETED', you have to manually alter the table and update the CHECK constraint. This takes time and makes maintenance harder as your app grows.

The Solution: Assertions

To fix this, Oracle 26ai introduces Assertions. Assertions handle validation automatically by checking your data against a lookup table.

Instead of hardcoding the rules, the assertion checks the lookup table in real-time. If you add 'DELETED' to your lookup table, the assertion automatically accepts it. You never have to change your main table structure again.

Prerequisites

  • Oracle 26ai Database.

  • Run the below command to grant you access from ADMIN:

GRANT CREATE ASSERTION TO DBX_ASSERTION;

Step 1: Create the Tables

CREATE TABLE ASSERTION_DEMO(
    ID                  NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOCACHE,
    NAME                VARCHAR2(200 CHAR),
    STATUS              VARCHAR2(20 CHAR) DEFAULT 'ACTIVE' NOT NULL CHECK (STATUS IN('ACTIVE','INACTIVE')),
    NEW_STATUS          VARCHAR2(20)
);

CREATE TABLE ASSERTION_STATUS_LOOKUPS(
    ID                  NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOCACHE,
    STATUS              VARCHAR2(200 CHAR),
    DESCRIPTION         VARCHAR2(2000 CHAR)
);

Step 2: Add Lookup Data

INSERT INTO ASSERTION_STATUS_LOOKUPS (STATUS, DESCRIPTION)
VALUES ('ACTIVE', 'Active');

INSERT INTO ASSERTION_STATUS_LOOKUPS (STATUS, DESCRIPTION)
VALUES ('INACTIVE', 'Inactive');

Step 3: Create the Assertion

CREATE ASSERTION check_new_status_valid
CHECK (
    ALL (
        SELECT NEW_STATUS
        FROM ASSERTION_DEMO
        WHERE NEW_STATUS IS NOT NULL
    ) demo_row
    SATISFY (
        EXISTS (
            SELECT 1
            FROM ASSERTION_STATUS_LOOKUPS l
            WHERE l.STATUS = demo_row.NEW_STATUS
              AND l.STATUS IS NOT NULL
        )
    )
);

Step 4: Test the Setup

-- 1. Valid insert (default STATUS = ACTIVE)
INSERT INTO ASSERTION_DEMO (NAME)
VALUES ('Record A');

-- 2. Valid insert with allowed STATUS
INSERT INTO ASSERTION_DEMO (NAME, STATUS)
VALUES ('Record B', 'INACTIVE');

-- 3. Valid NEW_STATUS (exists in lookup)
INSERT INTO ASSERTION_DEMO (NAME, STATUS, NEW_STATUS)
VALUES ('Record C', 'ACTIVE', 'INACTIVE');

-- 4. Valid NEW_STATUS
INSERT INTO ASSERTION_DEMO (NAME, STATUS, NEW_STATUS)
VALUES ('Record D', 'INACTIVE', 'ACTIVE');

-- 5. Invalid STATUS (fails because of the old CHECK constraint)
INSERT INTO ASSERTION_DEMO (NAME, STATUS)
VALUES ('Record E', 'DELETED');


-- 6. Invalid NEW_STATUS (fails because 'PENDING' is not in our lookup table)
INSERT INTO ASSERTION_DEMO (NAME, STATUS, NEW_STATUS)
VALUES ('Record F', 'ACTIVE', 'PENDING');


--7. Insert PENDING into lookup
INSERT INTO ASSERTION_STATUS_LOOKUPS (STATUS, DESCRIPTION)
VALUES ('PENDING', 'Pending');

--8. Try again step 6 - Success
INSERT INTO ASSERTION_DEMO (NAME, STATUS, NEW_STATUS)
VALUES ('Record F', 'ACTIVE', 'PENDING');


--9. If the child table having data, then the lookup table row cannot be updated or deleted
UPDATE ASSERTION_STATUS_LOOKUPS SET STATUS = 'DELETED'
WHERE STATUS = 'PENDING';
commit;


--10. Removing the child table record and update lookup table row
DELETE FROM ASSERTION_DEMO WHERE NEW_STATUS = 'PENDING';

UPDATE ASSERTION_STATUS_LOOKUPS SET STATUS = 'DELETED'
WHERE STATUS = 'PENDING';


--11. Update child table - status not available in lookup - (fails because 'ARCHIEVED' is not in our lookup table)
UPDATE ASSERTION_DEMO SET NEW_STATUS = 'ARCHIEVED'
WHERE NEW_STATUS = 'ACTIVE';

Conclusion

In short, Oracle 26ai Assertions make checking your data much easier. Here is why they are helpful:

  • No fixed rules: You do not have to lock rules directly into your table design.

  • Fast updates: Need to add a new status? Just add a new row to your lookup table. The database does the rest.

  • Less risk: You avoid changing live tables just to update a simple rule.

  • Cleaner setup: Your main tables stay clean, and your rules are easy to find and manage.

With Assertions, your database can easily grow with your business needs without extra coding.


Hope you learned something new. 😊

Comments

Post a Comment

Popular posts from this blog

Configuring email in Oracle APEX

Image
Reason for wrote this post:     From  Mylearn Workspace  we can easily send mails without any obstacles. But in our OCI free tier account we need to do some initial setup for sending email from APEX workspace or DB. Else it will be in mail queue itself. What you will get:     Basic idea of email triggering from DB. How to configure default sender email and send test email from OCI free tier APEX workspace. Steps involved:     1.  Create user and generate SMTP credentials     2.  Getting Public end point URL     3.  Updating Instance settings in Administration     4.  Validating the configuration Create user and generate SMTP credentials In left navigation menu go to the Identity & Security tab navigate to Domain section. Under Default domain go to Users tab and Create User with required details (we can go with default user also, for the testing purpose I created new user and used it) Once th...
Read more

Creating new ATP instance & Login to APEX

Image
What you will get from this post:       A complete guidance to create an Autonomous Transaction Processing database and login to the Oracle APEX. Also we'll cover how to connect Cloud DB with local SQL Developer. Steps to be followed: After successful signup process, we'll be redirected to the OCI home page From the home page left side menu go to Identity & Security tab and find Compartments . For creating ATP instance its not necessary to create a compartment. But for the formal way we should not create DB under a root directory. Under compartments tab create a new Compartment under root. Then navigate to left side panel and choose Oracle Database & click  Autonomous Transaction Processing and select the compartment which you created the above.  Click Create Autonomous Database & provide required details. Choose Always Free configuration options & network access type as Secure access from everywhere. These are steps we need to follo...
Read more