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.

Collaborate14

Presenting at

My Book

Speaking at:

It's All About Passion

Oracle ACE. Exadata Certified Specialist, Oracle Blogger, Oracle DBA, OCP in 11g DBA and 10g DBA and working in Oracle products since 2001. Speaker at conferences like Oracle Open World, UKOUG, Collaborate, Insync etc around the globe. Currently working with Pythian Australia as Senior Oracle DBA.