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.

Cabasset

Why would you need this? BTW, maybe you should accept some answers to previous questions that you posted. – Alston

1) Actually, there are 2 or 3 SO posts that talk about just that, the why. In my case I am adding a version table, and want to enforce the rule that database version scripts need to update rather than add an extra row to it. In time, version-checking queries will depend upon this. 2) I have not let myself close questions for which I have gotten answers, but not the answer to the question I asked. I'll review them again though, it's possible I've missed something. – Cabasset

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.

Thoroughpaced

+1: I like your index idea, simple and efficient. I'm not sure the READ ONLY table would allow updates though... – Impolitic

I've already done the dummy column and constraint, but I like this idiom. The only thing left is to test whether export can't handle it (as it can't handle virtual columns which also rely on functions). – Cabasset

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.

Boeke

I wish I could give you the point for all your effort, but the other guy answered the question better. Thanks for the list of options, I'm sure I'll refer back here! – Cabasset

No worries, I gave him my vote too :) and learned something in the process! – Boeke

Answer 3

CREATE TABLE Singleton
(x INTEGER NOT NULL UNIQUE CHECK (x = 1),
 col1 INTEGER NOT NULL);

(untested)

SQL doesn't have a concise syntax to specify the empty set of attributes as a key, which is a shame because it would eliminate the need for the redundant extra column. The effective key here actually IS the empty set, which is why I've used a UNIQUE constraint instead of PRIMARY KEY for the constraint on x.

Kreiker

This is the solution in my 2nd paragraph. – Cabasset