Is there any way to restrict the number of inserted rows?

Question

Oracle newbie here. I need to build a database which fulfils the requirements below:

A department is allowed to register for only two programs in a year
The maximum participants in each program must not exceed the number of people in respective departments. *There are 14 departments in total.

As per requirement, seems like I have to restrict the number of rows inserted. For example, if the total number of people in Department A is 100, the 101st row has to be rejected.

Apologies if there are many errors as I'm writing this question because now is 1.30AM. I tried to keep the table simple with less columns so it's easier to test the code.

CREATE TABLE department(
DEPT_ID CHAR(5) not null primary key,
TOTAL_P NUMBER);

CREATE TABLE participant(
P_ID CHAR(5) not null primary key,
DEPT_ID CHAR(5) not null);

CREATE TABLE program(
PROG_ID CHAR(5) not null primary key,
PROG_NAME VARCHAR(30),
DEPT_ID CHAR(5),
START_DATE DATE,
END_DATE DATE,
FOREIGN KEY(DEPT_ID) references department(DEPT_ID) on delete cascade);

and I have tried using trigger, but I keep getting warning: trigger created with compilation errors.

(I tried to count the rows in table program and group them by dept_id, then proceed to check the condition)

CREATE OR REPLACE TRIGGER prog
    BEFORE INSERT ON program
DECLARE
    CountRows NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO CountRows
    GROUP BY DEPT_ID
    FROM program;

    IF CountRows > 2 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Only 2 programs are allowed');    
    END IF;
END;
/

I don't even know if my idea does make sense or not. I tried many other ways like putting the condition where(to specify dept_id) before begin, after begin, I still get the warning. I have been experimenting a whole day and still cannot figure it out.

MY QUESTIONS:

Is it better to create multiple conditions in one trigger as I will have 14 departments? if so, how to do that without getting the warning?
any alternative way to restrict the number of rows?

Any help, hints, tips, anything, is deeply appreciated. thanks.

Just to mention in passing, [don't use CHAR](https://stackoverflow.com/a/42165653/230471). — William Robertson, Jan 18 '23 at 23:26
@WilliamRobertson thanks, my lecturer never mentions about that. — k4sen3, Jan 19 '23 at 02:32
@k4sen3 `char` always reserves space on the disk for all positions in the column, whether they are used or not. This can add up to a lot of empty space that must be scanned in disk I/O or into memory when pulling the actual data in a query. Using `varchar2` is preferred because it only takes space for real data, never empty space. This typically reduces the I/O overhead and memory required. — pmdba, Jan 19 '23 at 12:51
CHAR is a frankly weird data type that is only useful if you need to blank pad every value, which nobody has ever needed to do. Set dept_id to `'X'` and it will store `'X '`. Obviously that’s not very useful. I really can’t understand why anyone would use it for anything. — William Robertson, Jan 20 '23 at 22:03
@WilliamRobertson you must be young enough to never have had the fortune of working with fixed width data files :) — Kurt, Jan 20 '23 at 22:26
@Kurt I wish. Fixed length died out once people stated loading files into databases in the 1980s so I only caught the end of it on legacy migration projects. But the point of that was so that your Cobol program could compute the location of a specific field in a data file and simply advance that number of bytes without the overhead of parsing line terminators and field delimiters etc, so it still doesn’t explain what a database with an `rpad` function would need it for. — William Robertson, Jan 21 '23 at 12:25

score 1 · Answer 1 · answered Jan 18 '23 at 19:08

You could do it by maintaining in the trigger handling insert/update/delete operation on the "child" table (e.g. the program), an intersection table "parent_child" (e.g. department_program) containing the 2 foreign keys on the parent/child tables, and an index on which you will put the check constraint (e.g. < 3 for the number of program per department) + any other column defining the scope of the constraint (e.g. here the year of the start_date of the program). The 2 columns with the FK, the index and the other scope columns should be the PK of this intersection table. e.g.

CREATE TABLE program_department
(   
    DEPT_ID CHAR(5),
    PROG_ID CHAR(5), 
    PROG_YEAR NUMBER(4), 
    PROG_IDX NUMBER(10,0) DEFAULT 0, 
    -- to force always equal to a number the constraint must be defererrable 
    CONSTRAINT CK_PROG_IDX CHECK (PROG_IDX >= 0 AND PROG_IDX < 3) ENABLE, 
    PRIMARY KEY (PROG_ID, DEPT_ID, PROG_YEAR, PROG_CNT)
)
;

The idea is to maintain the PROG_IDX that will contain the index of the relation between the department and the program of the specific year. In the trigger on the table program, you have to update the program_department according to each action, when updating/removing this may imply/implies decrementing the PROG_IDX of the ones having PROG_IDX greater than the one removed.

And of course you will have to apply about the "same" logic for the participant's relationship, however there you can't hardcode the constrain by a CHECK since the # of people in each department is not known at compile time. This case is more complex also because you have to think about the consequence of changes of the # of persons in a department. Probably you will have to keep in the intersection table, the # of people in the department at the start_date of the program.

Is there any way to restrict the number of inserted rows?

1 Answers1