1 2 Previous Next 20 Replies Latest reply: Aug 29, 2010 2:24 PM by orawiss RSS

    Table with only one row

    user10484841
      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
          Salman Qureshi
          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
            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
              Salman Qureshi
              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
                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
                  Salman Qureshi
                  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
                    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
                      Billy~Verreynne
                      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
                        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
                          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
                            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
                              Charles Hooper
                              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
                                Cool idea, Charles! I never even considered a function based index on a constant!

                                -Mark
                                • 13. Re: Table with only one row
                                  mbobak
                                  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
                                    Billy~Verreynne
                                    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