Oracle: constraint preventing insert more than N (variable) rows

Question

I've got a table defining the max number of objects for each customer.

• Table_1

  • id_table_1 numeric primary key
  • cd_object varchar2(20)
  • max_number number

Another table stores the object assigned to each customer

• Table_2
  • id_table_2 numeric primary key
  • cd_customer varchar2(20)
  • cd_object varchar2(20)

How can I set up a constraint in table_2 in order to prevent more than "max_number" record for each "customer - object" couple?

For example:

Table_1

cd_object / max_number

xxx / 1

yyy / 2

Table_2

insert "customer_1", "xxx" -> OK!

insert "customer_1", "xxx" -> KO!

insert "customer_1", "yyy" -> OK!

insert "customer_1", "yyy" -> OK!

insert "customer_1", "yyy" -> KO!

Thanks in advance for your replies.

Answer 1

You can use the trigger on TABLE_2 as following:

-- creating the tables

SQL> CREATE TABLE TABLE_1 (
  2      ID_TABLE_1   NUMBER PRIMARY KEY,
  3      CD_OBJECT    VARCHAR2(20),
  4      MAX_NUMBER   NUMBER
  5  );

Table created.

SQL> CREATE TABLE TABLE_2 (
  2      ID_TABLE_2    NUMBER PRIMARY KEY,
  3      CD_CUSTOMER   VARCHAR2(20),
  4      CD_OBJECT     VARCHAR2(20)
  5  );

Table created.

-- creating the trigger

SQL> CREATE OR REPLACE TRIGGER TRG_TABLE_2_MAX_OBJECT BEFORE
  2      INSERT OR UPDATE ON TABLE_2
  3      FOR EACH ROW
  4  DECLARE
  5      LV_MAX_NUMBER   TABLE_1.MAX_NUMBER%TYPE;
  6      LV_COUNT        NUMBER;
  7  BEGIN
  8      BEGIN
  9          SELECT
 10              MAX_NUMBER
 11          INTO LV_MAX_NUMBER
 12          FROM
 13              TABLE_1
 14          WHERE
 15              CD_OBJECT = :NEW.CD_OBJECT;
 16
 17      EXCEPTION
 18          WHEN OTHERS THEN
 19              LV_MAX_NUMBER := -1;
 20      END;
 21
 22      SELECT
 23          COUNT(1)
 24      INTO LV_COUNT
 25      FROM
 26          TABLE_2
 27      WHERE
 28          CD_OBJECT = :NEW.CD_OBJECT;
 29
 30      IF LV_MAX_NUMBER = LV_COUNT AND LV_MAX_NUMBER >= 0 THEN
 31          RAISE_APPLICATION_ERROR(-20000, 'Not allowed - KO');
 32      END IF;
 33
 34  END;
 35  /

Trigger created.

-- testing the code

SQL> INSERT INTO TABLE_1 VALUES (1,'xxx',1);

1 row created.

SQL> INSERT INTO TABLE_1 VALUES (2,'yyy',2);

1 row created.

SQL> INSERT INTO TABLE_2 VALUES (1,'customer_1','xxx');

1 row created.

SQL> INSERT INTO TABLE_2 VALUES (2,'customer_1','xxx');
INSERT INTO TABLE_2 VALUES (2,'customer_1','xxx')
            *
ERROR at line 1:
ORA-20000: Not allowed - KO
ORA-06512: at "TEJASH.TRG_TABLE_2_MAX_OBJECT", line 28
ORA-04088: error during execution of trigger 'TEJASH.TRG_TABLE_2_MAX_OBJECT'


SQL> INSERT INTO TABLE_2 VALUES (3,'customer_1','yyy');

1 row created.

SQL> INSERT INTO TABLE_2 VALUES (4,'customer_1','yyy');

1 row created.

SQL> INSERT INTO TABLE_2 VALUES (5,'customer_1','yyy');
INSERT INTO TABLE_2 VALUES (5,'customer_1','yyy')
            *
ERROR at line 1:
ORA-20000: Not allowed - KO
ORA-06512: at "TEJASH.TRG_TABLE_2_MAX_OBJECT", line 28
ORA-04088: error during execution of trigger 'TEJASH.TRG_TABLE_2_MAX_OBJECT'


SQL>

-- Checking the data in the TABLE_2

SQL> SELECT * FROM TABLE_2;

ID_TABLE_2 CD_CUSTOMER          CD_OBJECT
---------- -------------------- --------------------
         1 customer_1           xxx
         3 customer_1           yyy
         4 customer_1           yyy

SQL>

Cheers!!

Answer 2

This constraint is more complex than a CHECK constraint can handle. One day we hope Oracle will support SQL ASSERTIONS which are constraints of arbitrary complexity.

Meanwhile this can be done (with caution re performance) using materialized views (MVs) and constraints. I blogged about this may years ago: your requirement is very similar to my example 3 there. Applying to your case it would be something like:

create materialized view table_2_mv
build immediate
refresh complete on commit as
  select t2.cd_customer, t2.cd_object, t1.max_number, count(*) cnt
  from table_2 t2
  join table_1 t1 on t1.cd_object = t2.cd_object
  group by t2.cd_customer, t2.cd_object, t1.max_number;

alter table table_2_mv
add constraint table_2_mv_chk
check (cnt <= max_number)
deferrable;

Pure trigger-based solutions tend to fail in the real world as when 2 users similtaneously add a record that just takes the count to the maximum, both succeed and when committed leave the table with more rows than the maximum in it!

However, taking into account your comment that you have 2M rows in table_2, which perhaps makes the MV approach above unusable, there could be another approach that does involve triggers:

1. Create a table that denormalizes information from table_1 and table_2 like this:

    create table denorm as
          select t2.cd_customer, t2.cd_object, t1.max_number, count(*) cnt
          from table_2 t2
          join table_1 t1 on t1.cd_object = t2.cd_object
          group by t2.cd_customer, t2.cd_object, t1.max_number;

2. Use a trigger or triggers on table_1 to ensure denorm.max_number is always correct - e.g when table_1.max_number is updated to a new value, update the corresponding denorm table rows.

3. Use a trigger or triggers on table_2 to update the denorm.cnt value - e.g. when a row is added, increment denorm.cnt, when a row is deleted, decrement it.

4. Add a check constraint to denorm

    alter table denorm
    add constraint denorm_chk
    check (cnt <= max_number);

This is essentially the same as the MV solution, but avoids the full refresh by using triggers to maintain the denorm table as you go along. It works in a multi-user system because the updates to the denorm table serialize changes to table_2 so that 2 users cannot modify it simultaneously and break the rules.