CODEWITHSUNDEEP
sqloracle-databasedatabase-designoracle11greferential-integrity
orbfish
orbfish

Reputation: 7731

Simple constraint to limit an Oracle table to a single row

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.

Upvotes: 11

Views: 4606

Answers (3)

nvogel
nvogel

Reputation: 25534

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.

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 17

Ronnis
Ronnis

Reputation: 12843

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.

Upvotes: 6

Related Questions