Simple constraint to limit an Oracle table to a single row

Question

I'm looking for a simple way to limit an application configuration table to 1 row. I know I can do this with a virtual column containing a subquery, or a materialized view on top, but really it's a simple table I'd like a constraint that doesn't take more lines to implement than to create the table.

Simplest way I've thought of is an ID field set to 1, and a check constraint that the ID has to be 1 - but this adds a confusing extra field to the table so I'm hoping there's something obvious I'm missing, like a check constraint where UNIQUE_FIELD = MAX(UNIQUE_FIELD), which is not allowed.

Answer 1

Simplest is a unique function-based index on a constant

> create unique index table_uk on one_row_table ('1');

Alternatives:

Rather than a table, you could have a view over DUAL That would really mean any UPDATE would actually be a CREATE OR REPLACE VIEW which may not be appropriate. Functions returning values from a package body or global application contexts might be a workaround for that if it causes invalidation problems.

With 11g a READ ONLY table (or, in earlier versions, a table in a read only tablespace) is an option.

Answer 2

Here are some options:

• Revoke the INSERT privileges on that table.
• Manage all access to the configuration table through procedures
• Replace the configuration table with a view that hides a column with a check constraint (col=1) and a unique constraint.
• Create that materialized view anyway
• Create a table trigger the fires on INSERT that throws an exception
• Rethink the table structure and add a FROM_DATE, so that the table is NOT updated but you create a new row every time. Your current configuration will be the row with the highest from_date. A current_config view over that table is useful.
• Replace the table with a view defined as SELECT 1024 as buffer_size, '/var/tmp' as temp_dir, 'other constant' as other_constant from dual. This isn't a solid solution.