💡
That's where one of the latest Oracle Database features - Assertions - came to help. It allowed us to achieve exactly the kind of constraint we were looking for. A schema-level integrity constraint that the database enforces on change, not just on inserts into one table.
Here is a test setup, similar to ours (not a perfect data model though, but great to show the kind of problem we needed to solve):
> We have a schema with two different tables holding the information about aircraft - `owned` aircraft and `leased` aircraft:
>
> * `OWNED_AIRCRAFT` - aircraft that the company owns. Aircraft\_IDs are prefixed `OWN-`.
>
> * `LEASED_AIRCRAFT` - aircraft that are leased from a third party. Aircraft\_IDs are prefixed `LSE-`.
>
> * A `FLIGHT_ASSIGNMENTS` table that keeps record of which aircraft flies which flight, using an `AIRCRAFT_ID` column. That ID should always resolve to **exactly one** of the two source tables:
>
>
> * * *
>
> * If `AIRCRAFT_ID` does not exist in either `OWNED_AIRCRAFT` or `LEASED_AIRCRAFT` - it's a data entry error.
>
> * Also, `AIRCRAFT_ID` can't be null.
>
![](https://cdn.hashnode.com/uploads/covers/6332d096fc1e3e483bf547c8/abbf305c-c314-4d76-bcac-2f3ffd935de7.png align="center")
## Before the assertion
To help me demonstrate the Assertions better, I have inserted 4 records - two aircraft in each of `owned_aircraft` and `leased_aircraft` tables:
```sql
insert into owned_aircraft (aircraft_id, tail_number, owner_name) values ('OWN-100', 'N100AB', 'Northwind Aviation');
insert into owned_aircraft (aircraft_id, tail_number, owner_name) values ('OWN-101', 'N101AB', 'Northwind Aviation');
insert into leased_aircraft (aircraft_id, tail_number, lessor_name) values ('LSE-200', 'N200LS', 'SkyLease Partners');
insert into leased_aircraft (aircraft_id, tail_number, lessor_name) values ('LSE-207', 'N207LS', 'SkyLease Partners');
```
With no assertion in place, the invalid data gets inserted successfully into `flight_assignments`, which is **wrong** (from business point of view) and should not be allowed:
```sql
-- Unknown entry: XYZ-999 does not exist in any of the two tables.
insert into flight_assignments (aircraft_id, flight_number, flight_date)
values ('XYZ-999', 'AS 100', sysdate);
```
The statement commits without any error, because nothing checks the cross-table reference.
## The assertion
### Aircraft\_ID in any of the parent tables
```sql
create assertion flight_aircraft_one_source
check (
not exists (
select 'An aircraft that does not exist in Owned or Leased aircraft tables'
from flight_assignments fa
where not exists (select 'owned' from owned_aircraft oa where oa.aircraft_id = fa.aircraft_id)
and not exists (select 'leased' from leased_aircraft la where la.aircraft_id = fa.aircraft_id)
)
);
```
This is the so-called `existential expression` - one of the two forms `CREATE ASSERTION` supports (the other one is the newer `ALL ... SATISFY` form). You can read the above expression as:
> "It must never be true that some flight's aircraft is in neither `owned_aircraft` nor `leased_aircraft`."