This content has been marked as final. Show 20 replies
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; /
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:
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.
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;
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,
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?
Ok, first the read consistency problem.
I have a test script, based on your trigger (slightly modified):
So, there's an empty table, and the trigger is in place. If we test it, we see:
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; /
And, as expected, the trigger does it's job, and I'm not permitted to insert a second row.
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'
No problem, right? But, what if we do this:
So, I truncated the table, to be sure it was empty, and I inserted one row. Note that I have not yet committed.
SQL> truncate table my_table; Table truncated. SQL> insert into my_table values(1); 1 row created.
Now, in a second session, I do:
Hmm...I just inserted another row, from a different session.
SQL> insert into my_table values(2); 1 row created. SQL> commit; Commit complete.
Now, go back to the first session and commit, and, voila!, two rows in the table!
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.
SQL> commit; Commit complete. SQL> select * from my_table; A ---------- 1 2
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.
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 :)
sb92075 wrote:Check again?
Last time I checked, DUAL is not restricted to a single row.
/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>
user10484841 wrote: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:
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?
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.
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 ;
You could use a pre-delete trigger to make sure that the row can't be deleted.
Jonathan Lewis wrote:Count(*) returns 1 but (and this is only for fun) look below
Billy Verreynne wrote:But what do you get if you try: "select count(*) from dual;" ?
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>
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
Another possible method might use a unique function based index:
We now have a table with a unique function based index on a constant. Now the test:
CREATE TABLE T1 (C1 NUMBER); CREATE UNIQUE INDEX T1_ONE_ROW ON T1('1');
Only 1 row in this session, let's try another session without committing in the first session:
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
INSERT INTO T1 VALUES (2); (Session 2 is hung)
Session 2 shows:
Let's try again with a different number:
INSERT INTO T1 VALUES (2) * 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"
INSERT INTO T1 VALUES (3); INSERT INTO T1 VALUES (3) * ERROR at line 1: ORA-00001: unique constraint (TESTUSER.T1_ONE_ROW) violated
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Jonathan Lewis wrote: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! ;-)
But what do you get if you try: "select count(*) from dual;" ?
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.