CODEWITHSUNDEEP
sqloracle-databasecheck-constraints
Matt
Matt

Reputation: 826

How to call a PL SQL function within a CHECK statement?

I would like to add a CHECK statement that calls a function when inserting new entries into a table. I have used the following sample code to implement such functionality:

CREATE TABLE customers(
    id NUMBER NOT NULL,
    PRIMARY KEY(id));

CREATE OR REPLACE FUNCTION totalCustomers
RETURN NUMBER IS 
   total NUMBER := 0; 
BEGIN 
   SELECT count(*) into total 
   FROM customers; 

   RETURN total; 
END; 
/

ALTER TABLE customers
ADD CHECK (totalCustomers() < 10);

When I run this query in livesql.oracle.com, I get the following error:

ORA-00904: "TOTALCUSTOMERS": invalid identifier.

What is the right way of calling this function in the check statement?

P.S. Please ignore the contents of the function. I will replace it with the desired contents later.

Upvotes: 1

Views: 1095

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

I would suggest a trigger for such requirement.

CREATE OR REPLACE TRIGGER AI_customers 
    AFTER INSERT ON customers 
DECLARE

total NUMBER; 

BEGIN
   SELECT count(*) into total 
   FROM customers; 
   IF total > 10 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Total number of customers must not exceed 10');
   END IF;
END;
/

Note, this is a STATEMENT LEVEL trigger (no FOR EACH ROW clause), thus you cannot get the famous "ORA-04091: table is mutating, trigger/function may not see it" error.

However, this trigger has some limitations in a multi-user environment. If user_1 inserts records into customers table and user_2 also inserts some records (before user_1 has done a COMMIT) then you may get more than 10 records in your customers table.

Upvotes: 1

Stew Ashton
Stew Ashton

Reputation: 1529

In your comment on mathguy's answer, you say "I am also trying to make sure the time period of new entries don't overlap with existing ones." I have done this with "refresh fast on commit" materialized views. Warning: "fast" refreshes can be slow if you are not careful, please refer to this blog http://www.adellera.it/ , especially concerning statistics on the materialized view log.

I am assuming exclusive end dates. If an end date is null, that means the datetime range goes on indefinitely. Many overlaps will be caught immediately by the primary key and unique constraints. The others will be caught at commit time by the constraint on the materialized view. Note that at the end of the transaction the MV will never have any rows.

SQL> create table date_ranges (
  2    key1, start_date,
  3    primary key(key1, start_date),
  4    end_date,
  5    unique(key1, end_date),
  6    check(start_date < end_date)
  7  )
  8  as
  9  with a as (select date '2000-01-01' dte from dual)
 10  select 1, dte, dte+1 from a
 11  union all
 12  select 1, dte+1, dte+2 from a
 13  union all
 14  select 1, dte-1, dte from a
 15  union all
 16  select 2, dte+10, dte+11 from a
 17  union all
 18  select 2, dte+12, dte+13 from a
 19  union all
 20  select 2, dte+8, dte+9 from a
 21  /

Table DATE_RANGES created.

SQL> create materialized view log on date_ranges
  2  with sequence, rowid, primary key, commit scn (end_date) including new values
  3  /

Materialized view log DATE_RANGES created.

SQL> create materialized view overlapping_ranges refresh fast on commit
  2  as
  3  select a.rowid arid, b.rowid brid
  4  from date_ranges a, date_ranges b
  5  where a.key1 = b.key1
  6  and a.rowid != b.rowid
  7  and a.start_date < b.end_date
  8  and a.end_date > b.start_date;

Materialized view OVERLAPPING_RANGES created.

SQL> 
SQL> alter materialized view overlapping_ranges
  2  add constraint overlaps_not_allowed check (1=0) deferrable initially deferred
  3  /

Materialized view OVERLAPPING_RANGES altered.

SQL> insert into date_ranges select 1, date '1999-12-30', date '2000-01-4' from dual;

1 row inserted.

SQL> commit;

Error starting at line : 42 in command -
commit
Error report -
ORA-02091: transaction rolled back
ORA-02290: check constraint (STEW.OVERLAPS_NOT_ALLOWED) violated

Upvotes: 2

user5683823
user5683823

Reputation:

There isn't one.

Straight from the Oracle documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/constraint.html#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE

Conditions of check constraints cannot contain the following constructs:

.............

  • Calls to user-defined functions

..............

Now: you said "disregard the actual content of the function". That is not a healthy attitude; the content matters too. For one thing, the function would have to be deterministic anyway (yours is not) - this is a problem quite apart from it being a user-defined function. Moreover, conditions in constraints can only refer to values in a single row - they can't be "table" constraints, like yours is.

You may wonder, then - how would one implement a "constraint" like yours? One somewhat common method is to create a materialized view based on "select count(*)....." and put a constraint on the MV. The MV should refresh full on commit. Whenever you modify the base table and you commit. the MV is refreshed - and if the count increases above 10, the changes are rolled back.

Upvotes: 4

Related Questions