Creating a table with max number of rows (ORACLE)

Question

Is there any way to limit the maximum number of rows when I create a table in Oracle?

Answer 1

You could create a statement trigger "BEFORE INSERT" and check there the nubmer of rows.

CREATE OR REPLACE TRIGGER BI_MY_TABLE
    BEFORE INSERT ON MY_TABLE
DECLARE
    CountRows NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO CountRows
    FROM MY_TABLE;

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

However, this trigger does not work properly in a multi-user environment.

Answer 2

If the table has a numeric key, you could add a check constraint that states that the key does not exceed a specific value:

ALTER TABLE turnip
ADD CONSTRAINT check_turnip_id
CHECK (turnip_id <= 50)
/