11

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.

orbfish
  • 7,381
  • 14
  • 58
  • 75
  • Why would you need this? BTW, maybe you should accept some answers to previous questions that you posted. – HardCode Mar 29 '11 at 19:40
  • 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. – orbfish Mar 29 '11 at 19:43

3 Answers3

17

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.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • +1: I like your index idea, simple and efficient. I'm not sure the READ ONLY table would allow updates though... – Vincent Malgrat Mar 30 '11 at 08:29
  • 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). – orbfish Apr 02 '11 at 17:34
6

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.
Ronnis
  • 12,593
  • 2
  • 32
  • 52
  • 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! – orbfish Apr 02 '11 at 17:33
  • No worries, I gave him my vote too :) and learned something in the process! – Ronnis Apr 02 '11 at 17:54
0
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.

nvogel
  • 24,981
  • 1
  • 44
  • 82