Saturday, August 28, 2010

Ensuring Table With Only One Row in Oracle Using Virtual Column

There was a discussion on the OTN General database forum, in which the OP asked creating a table with just one row and restricting that table to just one row. Here is my attempt at it.

I created a table with two columns, and the second column is a virtual column and contains a constant. I created a unique index on this column. On every insertion, this second column always evaluates to 1, and unique index (which become the function based index on virtual column) ensures that only one row remains in the table.


oracle@test # sqlplus /nolog
 
SQL*Plus: Release 11.1.0.7.0
 - Production on Sat Aug 28 19:09:16 2010
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
idle> conn test/test
Connected.
test@test> create table t1
 (c1 number, c2 generated always as (1) virtual);
 
Table created.
 
test@test> create unique index idx1 on t1(c2);
 
Index created.
 
test@test> insert into t1(c1) values (1);
 
1 row created.
 
test@test> commit;
 
Commit complete.
 
test@test> insert into t1(c1) values (1);
insert into t1(c1) values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.IDX1) violated
 
 
test@test> insert into t1(c1) values (2);
insert into t1(c1) values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.IDX1) violated

1 comment:

orawiss said...

hello Fahd,
I would suggest this :

Connected to
Connected as SYS

SQL> create table Onerow(a number);

Table created

SQL> insert into onerow values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> alter table onerow read only;

Table altered

SQL> insert into onerow values(2);

insert into onerow values(2)

ORA-12081: no se permite la operación de actualización en la tabla "SYS"."ONEROW"

SQL>

Thaksn,
Wissem EL KHLIFI
http://oracle-tns.com/