This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Aug 29, 2010 12:24 PM by orawiss RSS

Table with only one row

user10484841 Newbie
Currently Being Moderated
Hi,
I need to create a table with only one row. I will use the table to store settings. If there are more than one row there is a errors.
Is it possible to create table just with one row and put a restriction for adding new rows?

regards
  • 1. Re: Table with only one row
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    Write a trigger on this table on insert. If rowcount of the table is 0, then insert should be successful otherwise rollback the insert operation and/or raise exception
    create trigger test_trig before insert on my_table for each row
    declare
    a number;
    begin
    select count(*) into a from my_table ;
    
    if a = 0;
    then 
    null;
    else
    Raise_application_error(-20100,'Can not insert more than one row');
    end if;
    
    end;
    /
    Salman
  • 2. Re: Table with only one row
    mbobak Oracle ACE
    Currently Being Moderated
    Have you considered the impact of multi-versioning read consistency?

    Your solution will not work.

    However, depending on the content of the table, you should be able to enforce a single row table with constraints.

    Actually, let me re-phrase:
    You should be able to enforce a table with a maximum of one row, with constraints. Come to think of it, you should be able to enforce that a particular row is never deleted (or updated) for that matter, with a trigger.

    For example, the most well known single row table is DUAL. Suppose you want to create your own DUAL table in your schema.

    You may be able to do so this way:
    create table my_dual(dummy char(1) primary key);
    alter table my_dual add constraint one_row check(dummy='X');
    insert into my_dual values('X');
    commit;
    Now, in the above table, the primary key enforces uniqueness on the column, and the check constraint guarantees that the only value that can be stored is 'X'. The combined effect is to guarantee the table will never have more than one row. (Though it could have zero.) As mentioned, you should be able to deal with that via a trigger.

    Finally, recent versions of Oracle allow for read only tables. If you are on a recent version of Oracle (at least 11gR1, I believe), this should be much simpler. You can simply populate the table and set it to read only.

    Hope that helps,

    -Mark
  • 3. Re: Table with only one row
    SalmanQureshi Expert
    Currently Being Moderated
    Hello,
    What if user wants to modify the "X". Well, if one value has to be inserted once and the life time then your solution will be working, otherwise it will not because check constraint only allows to have 'X' as the value, what if i want to update it as 'Y'. OP has yet to explain if he also does not want to change this "X" for the life of this table
    Can you please explain where does multi versioning read consistency lie in current scenario?
    Thanks

    Salman
  • 4. Re: Table with only one row
    mbobak Oracle ACE
    Currently Being Moderated
    Hi Salman,

    Ok, first the read consistency problem.

    I have a test script, based on your trigger (slightly modified):
    drop table my_table;
    create table my_Table(a number);
    create or replace trigger test_trig before insert on my_table for each row
    declare
    a number;
    begin
    select count(*) into a from my_table ;
    if (a != 0)
    then
    raise_application_error(-20100,'Can not insert more than one row');
    end if;
    end;
    /
    So, there's an empty table, and the trigger is in place. If we test it, we see:
    SQL> @test_case
    
    Table dropped.
    
    
    Table created.
    
    
    Trigger created.
    
    SQL> insert into my_table values(1);
    
    1 row created.
    
    SQL> /
    insert into my_table values(1)
                *
    ERROR at line 1:
    ORA-20100: Can not insert more than one row
    ORA-06512: at "MBOBAK.TEST_TRIG", line 7
    ORA-04088: error during execution of trigger 'MBOBAK.TEST_TRIG'
    And, as expected, the trigger does it's job, and I'm not permitted to insert a second row.

    No problem, right? But, what if we do this:
    SQL> truncate table my_table;
    
    Table truncated.
    
    SQL> insert into my_table values(1);
    
    1 row created.
    So, I truncated the table, to be sure it was empty, and I inserted one row. Note that I have not yet committed.

    Now, in a second session, I do:
    SQL> insert into my_table values(2);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    Hmm...I just inserted another row, from a different session.

    Now, go back to the first session and commit, and, voila!, two rows in the table!
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from my_table;
    
             A
    ----------
             1
             2
    That's because each session can't see the other's uncommitted work. Hence, your trigger does not work. This is multi-versioning read consistency in action.

    As to your other concerns, you're right, there are significant limitations around what described above, and, depending on what the original poster's real requirements are, that may or may not be relevant.

    If the OP comes back with more detailed requirements, I may be able to offer a more complete solution.

    -Mark
  • 5. Re: Table with only one row
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    Thanks for the explain and highlighting limitation of this.
    In this case there are limitations.
    I this situation, the OP can consider having a procedure for insertion of value in this table and committing inside the procedure just after insertion(and firing of trigger) can also help solving this problem which you highlighted.
    Now ball is in OP's court :)

    Salman
  • 6. Re: Table with only one row
    sb92075 Guru
    Currently Being Moderated
    For example, the most well known single row table is DUAL.
    Last time I checked, DUAL is not restricted to a single row.
    With that being said, I must warn that Oracle may misbehave when DUAL has more than 1 row.
  • 7. Re: Table with only one row
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    sb92075 wrote:

    Last time I checked, DUAL is not restricted to a single row.
    Check again?
    /home/billy> sqlplus "/ as sysdba"
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 26 07:31:04 2010
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    
    SQL> select * from dual;
    
    DUM
    ---
    X
    
    SQL> insert into dual ( dummy ) values ( 'Z' );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from dual;
    
    DUM
    ---
    X
    
    SQL>
  • 8. Re: Table with only one row
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Billy  Verreynne  wrote:
    SQL> select * from dual;
    
    DUM
    ---
    X
    
    SQL> insert into dual ( dummy ) values ( 'Z' );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from dual;
    
    DUM
    ---
    X
    
    SQL>
    But what do you get if you try: "select count(*) from dual;" ?

    Regards
    Jonathan Lewis
  • 9. Re: Table with only one row
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user10484841 wrote:
    Hi,
    I need to create a table with only one row. I will use the table to store settings. If there are more than one row there is a errors.
    Is it possible to create table just with one row and put a restriction for adding new rows?
    A simple approach - just off the top of my head - if you want to be able to update the one row, but not allow a row to be inserted, is to use a view and hide a table behind it. Something like:
    create table hidden_one_row(
        id number(1,0) not null,
        col1 ..
        col2 ..
    ...
        colN
    );
    
    insert into hidden_one_row values(1, etc....);
    commit;
    
    create or replace view visible_one_row
    as
    select col1, col2 .. colN
    from hidden_one_row
    ;
    Allow access only to the view. Any attempt to insert a second row into the view will fail because the implied insert into the underlying table won't have a value for a column that's been declared not null.

    You could use a pre-delete trigger to make sure that the row can't be deleted.


    Regards
    Jonathan Lewis
  • 10. Re: Table with only one row
    user503699 Expert
    Currently Being Moderated
    Jonathan Lewis wrote:
    Billy  Verreynne  wrote:
    SQL> select * from dual;
    
    DUM
    ---
    X
    
    SQL> insert into dual ( dummy ) values ( 'Z' );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from dual;
    
    DUM
    ---
    X
    
    SQL>
    But what do you get if you try: "select count(*) from dual;" ?

    Regards
    Jonathan Lewis
    Count(*) returns 1 but (and this is only for fun) look below
    SQL> select * from v$version ;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE     10.2.0.1.0     Production
    TNS for Linux: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    
    SQL> select * from dual ;
    
    D
    -
    X
    
    SQL> insert into dual ( dummy ) values ( 'Z' );
    
    1 row created.
    
    SQL> select * from dual ;
    
    
    D
    -
    X
    
    
    SQL> select count(*) from dual ;
    
    
      COUNT(*)
    ----------
          1
    
    
    SQL> REM But try using the table data to, say, populate another table
    SQL> create table hr.my_dual as select * from dual ;
    
    Table created.
    
    SQL> select * from hr.my_dual ;
    
    D
    -
    X
    Z
    ;)
  • 11. Re: Table with only one row
    CharlesHooper Expert
    Currently Being Moderated
    Mark,

    Nice example.

    Another possible method might use a unique function based index:
    Session 1:
    CREATE TABLE T1 (C1 NUMBER);
     
    CREATE UNIQUE INDEX T1_ONE_ROW ON T1('1');
    We now have a table with a unique function based index on a constant. Now the test:

    Session 1:
    INSERT INTO T1 VALUES (1);
     
    1 row created.
     
    INSERT INTO T1 VALUES (2);
     
    ERROR at line 1:
    ORA-00001: unique constraint (TESTUSER.T1_ONE_ROW) violated
    Only 1 row in this session, let's try another session without committing in the first session:
    Session 2:
    INSERT INTO T1 VALUES (2);
    
    (Session 2 is hung)
    Session 1:
    COMMIT;
    Session 2 shows:
    INSERT INTO T1 VALUES (2)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (TESTUSER.T1_ONE_ROW) violated
    Let's try again with a different number:
    Session 2:
    INSERT INTO T1 VALUES (3);
    INSERT INTO T1 VALUES (3)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (TESTUSER.T1_ONE_ROW) violated
    The above was inspired by a solution found on page 472 of the book "Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition"

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 12. Re: Table with only one row
    mbobak Oracle ACE
    Currently Being Moderated
    Cool idea, Charles! I never even considered a function based index on a constant!

    -Mark
  • 13. Re: Table with only one row
    mbobak Oracle ACE
    Currently Being Moderated
    Very nice, Jonathan.

    I couldn't think of an elegant and efficient way to do this. I think you've nailed it.

    -Mark
  • 14. Re: Table with only one row
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Jonathan Lewis wrote:

    But what do you get if you try: "select count(*) from dual;" ?
    Sheez.. don't scare me like that! After trying it on my XE instance, I also tested in on a 11gr2 dev cluster just to confirm behaviour on 11g too! ;-)

    Luckily - the select count (still) returns 1. As the other poster showed, a CTAS on it shows the "hidden/added" rows. A delete of that added row from dual removes it and a CTAS will show only 1 row in dual again.

    Had a quick look through the ../rdbms/admin scripts, found a plain vanilla table definition for dual and nothing else that attempts to constrain it to 1 row. Seems like that this code is inside Oracle itself, as oppose to enforcing it via SQL? Kind of like a automatically adding a "+rownum=1+" predicate to it.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points