Ensuring Table With 1 Row in Oracle 11g Using Virtual Column

Posted in: Technical Track

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

Interested in working with Fahd? Schedule a tech call.

About the Author

I am in love with Oracle blogging since 2007. The blogging coupled with extensive participation in Oracle forums, plus Oracle related speaking engagements, various Oracle certifications, teaching, and working in the trenches with Oracle technologies enabled me to get Oracle ACE award. I was the first ever Pakistani to get that award. From Oracle Open World SF to Foresight 20:20 Perth, I have been expressing my love for Exadata. These days for the last some years, I am loving the data at Pythian, and proudly writing their log buffer carnivals.

3 Comments. Leave new

Hi,

simple unique index on any constant instead of column is simpler I think, and works on older versions too..
Here’s a sample

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create table t1 (c1 number);

Table created.

SQL> create unique index idx1 on t1(1);

Index created.

SQL> insert into t1(c1) values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1(c1) values (2);
insert into t1(c1) values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.IDX1) violated

Maris

Reply

This is brilliant. Pure genius. Thanks!

Reply
Timo Raitalaakso
September 1, 2010 5:13 am

I have been hitting some bugs while using constant numbers in virtual columns. Check your virtual column data type. Sometimes queries from such column return numbers from 0.9999 to 1.0000. Also seen situations where null returned. I would suggest casting your constant number one to a specific length while creating such table. c2 number(1) as (cast (1 as number(1)))

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *