Table must have one row minimum and 1 row maximum. (singularity check)

12

Imagine that you have to implement a table which must contain one row minimum and
may not have more than one rows. So, on insert of a row you can check if there’s
already a row in the table by joining the table to itself, like the next statement

select 1<br />from&nbsp;&nbsp; table t1<br />,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; table t2<br />where&nbsp; t1.id != t2.id

The obvious way to do this is to create a set....

of database triggers. You cannot use only one row trigger because the query is mutating,
so you need to put the rowid on a stack on a row event trigger and loop
over the rowid’s on stack on a after statement event.

Next create 3 triggers to prevent (sort of) that more than 1 row is
in the table at all times.
Because checking the record count involves a mutating table issue
the rowid is put on a stack and handled in the after statement trigger.
You need
1) a before statement trigger to initialize the stack
2) a (after) row trigger to put the new row on the stack.
  I prefer to use an after row trigger above before row because when the after row event fires all
  database constraints (when not flagged defered) are finished.
  Failures on these constraints are handled immediately and after row triggers do not fire,
  thus will cost nothing. It more expensive for a before row to fire when the row fails
  due to a constraint violation. So I prefer to let the database do it’s work before I will throw in
  some business rules checking.
3) a after statement trigger to perform the singularity check

Here’s the scripting:

create table ags_parameters<br />( par_id&nbsp;&nbsp;&nbsp; number(4)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; not null,<br />&nbsp; par_name&nbsp; varchar2(100 byte)<br />)<br />/
alter table ags_parameters<br />add constraint ags_par_pk primary key(par_id)<br />/<br /><br />create or replace trigger ags_par_bs<br />before delete or insert or update<br />on ags_parameters <br />declare<br />begin<br />&nbsp; if inserting or updating<br />&nbsp; then<br />&nbsp;&nbsp;&nbsp; acc_rowid_stack_light.create_stack;<br />&nbsp; end if;<br />exception<br />&nbsp; when others<br />&nbsp; then raise_application_error(-20010, sqlerrm );<br />end;<br />/
create or replace trigger ags_par_ar<br />after delete or insert or update<br />on ags_parameters<br />for each row<br />declare<br />&nbsp; flg_par1&nbsp; constant binary_integer := 1;<br />begin<br />&nbsp; if inserting<br />&nbsp; then<br />&nbsp;&nbsp;&nbsp; acc_rowid_stack_light.push_flag(:new.rowid, flg_par1);<br />&nbsp; elsif deleting<br />&nbsp; then<br />&nbsp;&nbsp;&nbsp; raise_application_error(-20010, 'Delete is not allowed' );<br />&nbsp; end if;<br />exception<br />&nbsp; when others<br />&nbsp; then raise_application_error(-20010, sqlerrm );<br />end ags_par_ar;<br />/<br /><br />create or replace trigger ags_par_as<br />after delete or insert or update<br />on ags_parameters <br />declare<br />&nbsp; flg_par1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; constant binary_integer := 1;<br />&nbsp; e_locked&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exception;<br />&nbsp; pragma&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exception_init (e_locked, -54);<br />&nbsp; t_rowid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rowid;<br />&nbsp; t_value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; binary_integer;<br />begin<br />&nbsp; while acc_rowid_stack_light.pop_flagset( t_rowid, t_value )<br />&nbsp; loop<br />&nbsp;&nbsp;&nbsp; if acc_rowid_stack_light.check_flag(t_value, flg_par1)<br />&nbsp;&nbsp;&nbsp; then<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; declare<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor c_par(b_rowid in rowid)<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; is<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select 1<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from&nbsp;&nbsp; ags_parameters par1<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ags_parameters par2<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where&nbsp; par1.par_id != par2.par_id<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and&nbsp;&nbsp;&nbsp; par1.rowid&nbsp;&nbsp; = b_rowid<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for update of par1.par_id;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r_par&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c_par%rowtype;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_par_found&nbsp; boolean;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; e_par&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exception;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; begin<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; open&nbsp; c_par(t_rowid);<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; fetch c_par into r_par;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; l_par_found&nbsp; := c_par%found;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; close c_par;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if l_par_found<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; then<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; raise e_par;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end if;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exception<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; when e_par<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; then raise_application_error(-20010, 'Only one row is allowed in this table');<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end;<br />&nbsp;&nbsp;&nbsp; end if;<br />&nbsp; end loop;<br />&nbsp; acc_rowid_stack_light.delete_stack;<br />exception<br />&nbsp; when others<br />&nbsp; then raise_application_error(-20010, sqlerrm );<br />end ags_par_as;<br />/

Now the testing.
First try to insert 2 records and see if it works.

insert into ags_parameters (par_name) values ('test1');<br />insert into ags_parameters (par_name) values ('test2');

The second row will raise an excpetion "ORA-20010 – Only one row is allowed in this table". That’s fine.
But is it?

Let’s try another experiment. This time I’m going to use 2 concurrent sessions.
In the first session I insert a row.

prompt Rollback previous test results.<br />rollback;<br />insert into ags_parameters (par_name) values ('session1');<br />pause;<br />commit;

It returns with "1 row created" Now do NOT commit! Open another session and also insert a row into ags_parameters and commit

prompt second session<br />insert into ags_parameters (par_name) values ('session2');<br />commit;

It also returns with "1 row created"

Return to the first session and commit that session also. Than count the records.

select count(*) from ags_parameters;

Oh no, 2 records ! ! ! !

Working alternative/solution

create table fbi_parameters<br />( par_id&nbsp;&nbsp;&nbsp; number(4)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; not null,<br />  par_name&nbsp; varchar2(100 byte)<br />)<br />/
alter table fbi_parameters <br />add constraint fbi_par_pk primary key(par_id)<br />/

Create a function based index. This index returns cq indexes value 1 for each and every record. Because it is
defined as a UNIQUE index, only one row is permitted in the table.

create unique index fbi_one_row_table_i on fbi_parameters (1)<br />/<br />insert into fbi_parameters (par_name) values ('session 1');<br />pause;<br />commit;

Do NOT commit! Open another session and also insert a row into fbi_parameters and commit

insert into ags_parameters(par_name) values ('session2');<br />pause;<br

 />commit;

It is immediately being locked by the other session.
Return to the first session and issue a commit;
Return to the second session and issue a commit;
The second session stops because "ORA-00001: unique constraint (fbi_one_row_table) violated"

select count(*) from fbi_parameters;

 

Yippie! Function based index to the rescue.
And as a side effect, less objects (1 table + 1 index + 3 triggers) vs (1 table + 2 indices + 0 triggers)

But that”s only part of the story: It is still possible to delete all record from fbi_parameters.
Here, I guess a database trigger is the only possible solution.

create or replace trigger fbi_par_br<br />after delete or insert or update<br />on ags_parameters<br />for each row<br />begin<br />&nbsp; if deleting<br />&nbsp; then<br />&nbsp;&nbsp;&nbsp; raise_application_error(-20010, 'Deleting the last/only row in this table is not allowed');<br />&nbsp; end if;<br />end fbi_par_br;<br />/

Table ags_parameters already has a check against deleting rows in the after row trigger. It might
as well be implemented as a before row trigger.

Share.

About Author

12 Comments

  1. Your solution is correct but there is no need to create an extra column . just pickup a non null column in the table and build a unique key by applying a single result function to it.

    Eg: Number —>CREATE UNIQUE INDEX tableX_ix ON tableX ( LEAST(0,ABS(fieldN)) );

    varchar2—->CREATE UNIQUE INDEX tableX_ix ON tableX ( LEAST(0,ASCII(substr(fieldC,1,1))) );

    …..

  2. This was a great help to me, I ended up creating table with a primary key that has a default value of 1 and a check constraint =1 (no Triggers necessary).

  3. Similar to Tobias, but returning to trigger type solution, how about:

    A) Create the table
    B) Insert one row into it.
    C) Create a trigger forbidding insert and delete as in…
    create or replace trigger trig_name
    before delete or insert on table_name
    for each row
    begin
    raise_application_error(-20010, ‘The table “TABLE_NAME” must always have one and only one row’);
    end;

    Alternativly create the table using a DBA’s account and only grant select,update to anyone else :)

  4. Harm Verschuren on

    @Tobias,
    So, now you have 2 concurrent sessions inserting into that table at the same time. Hoe many rows will you end up with?
    My guess: 2 rows.
    That’s what this post is all about.

  5. You can use roles:

    CREATE TABLE tablename (
    value1 INTEGER NOT NULL,
    value2 INTEGER NOT NULL
    );

    INSERT INTO tablename (value1, value2) VALUES (1, 2);

    CREATE RULE noDeleteRule AS ON DELETE TO tablename DO INSTEAD NOTHING;
    CREATE RULE noInsertRule AS ON INSERT TO tablename DO INSTEAD NOTHING;

    this work in postgresql 8.1

  6. Harm Verschuren on

    Besides less objects, a nice feature of the solutions given by Anton and LewisC is that it
    works on RDBMS from 7.3 up to 10g, while mine “only” works on RDBMS 8i up to 10g
    because of the function based index. All options work perfectly.

  7. Harm Verschuren on

    Hello Toon,
    The row must persist in all sessions even after a restart of the database.
    The row must be updateble (by privileged users).
    The maximum amount of rows in the table must be 1
    and the minimum amount of rows must be 1. In others words: one mandatory row.

  8. Toon Koppelaars on

    Harm,

    What *exactly* is the requirement here?
    A table with one row in it… could you not just implement the
    values that need be stored, as persistent package variables?
    Or should the values really persist across sessions?

    Toon

  9. The same, but in another way (1 table + 1 index + 1 trigger)

    create table test( id number(1) primary key, v1 varchar2(10) )

    create or replace trigger test_br
    before delete or insert or update on test
    for each row
    begin
    if inserting or updating
    then
    :new.id := 1;
    else
    raise_application_error(-20010, ‘Deleting the last/only row in this table is not allowed’);
    end if;
    end;

    Anton

  10. Hi,

    Very interesting problem. I think I have an alternative that is a bit less code. If I
    understand the requirement, I think you can do it with a unique index and 1 trigger.

    How about this:

    CREATE TABLE TEST_UK
    (
    FIELD1 NUMBER DEFAULT 1 NOT NULL,
    REAL_DATA VARCHAR2(10),
    MORE_DATA NUMBER
    );

    Field1 is a field not used by the application. It’s just along for the ride. Real_data and
    more_data are application fields.

    We create a Unique Key on field1.

    CREATE UNIQUE INDEX TEST_UK_IDX ON TEST_UK
    (FIELD1);

    And a single trigger on the table:

    CREATE OR REPLACE TRIGGER TEST_UK_TRG
    BEFORE DELETE OR INSERT OR UPDATE
    ON TEST_UK
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
    IF deleting THEN
    raise_application_error( -20001, ‘Cannot delete a row from this table’);
    ELSE
    :new.field1 := 1;
    END IF;
    END ;
    /

    You can ignore the field1 column in all updates and inserts. The trigger will make sure
    that this not null column is populated.

    I think that does it. A PK could be used instead of a unique key in this instance but by
    using a unique key, the PK remains available for use by the application. Although with
    a one row table, I wonder how important that really is.

    Thanks,

    LewisC