Question: What do
I do to use the SQL99 "create assertion" statements in
Oracle?
Answer: As of 12c, Oracle does not
support assertions, but there is some buzz that Oracle 13c
may contain assertions. Let's take a look at the
"create assertion" syntax and see how it might be helpful in
Oracle.
Formally, an assertion is defined as a way to implement
transaction level, cross record constraints, a way to
enforce complex data rules that span many tables. In a
nutshell, the create assertion syntax would move these
complex rules out of triggers (or application programs) and
place them directly in the database as a check constraint.
Currently, there are only three ways of implementing
cross-table row validation in Oracle:
- Validate in the application code
- Use an inert/update trigger
- Implement a materialized view
There is a great concern about the performance of DML
when using assertions because an application can pre-approve
batches of rows for inserts, while the assertion applies to
row-at-a-time DML. However, if you use deferrable
constraints, then there may be ways to circumvent the
performance issue.
First, the create assertion can be used to replace insert
triggers that validate incoming data rows. For
example, we might see a create assertion that restricts rows
in a table to a specific set of column values. This is
implemented by providing a SQL query.
The rules can range from very simple (each department can
only have one DBA) to extremely complex, and there is no
practical limit on the complexity since assertions are
implemented with a SQL statement.
Oracle provides one example of such a create assertion
query, checking column values in two tables and applying it
to a check constraint:
"This SQL statement creates an
assertion to disallow suppliers based in cities of Albany,
Palo Alto, or Portland from supplying, in quantities higher
than 50, all the parts that are red or cost $10.00 or more:"
create assertion AllPartSupp as
CHECK
(not exists
(select
'an s shipping all parts'
from SUPPLIER s
where s.CITY in ('Albany', 'Palo Alto', 'Portland')
and not exists
(select 'a p not shipped'
from PART p
where (p.COLOR = 'red' or p.PRICE >= 10)
and not exists
(select 'a connecting sh'
from SHIPMENT sh
where sh.QUANTITY > 50
and sh.SNO = s.SNO
and sh.PNO = p.PNO)))
)
It appears that assertions may be expensive to implement
in any database because the SQL statement must fire before
every insert on the target table, but the assertion approach
does remove the need to write triggers to enforce complex
data relationships between row columns in a table.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
