Re: limit TABLE to only one record

Brian Tkatch wrote:
> I am looking to store an easily modifiable value that a VIEW can use,
> without issuing any DDL statements. I'm guessing that a TABLE with one
> record would be a good way. The question then is how to limit a TABLE
> to only one record:
>
> CREATE TABLE One_Record_Only
> (
> Id INT DEFAULT 1,
> Data INT,
> CONSTRAINT ORO1_Id_NN CHECK(Id IS NOT NULL),
> CONSTRAINT ORO1_Id_CK CHECK(Id = 1),
> CONSTRAINT ORO1_Id_PK PRIMARY KEY(Id)
> USING INDEX (CREATE UNIQUE INDEX ORO1_Id_PK ON One_Record_Only(Id))
> );
>
> Is there a more straightforward emthod to limit a TABLE to only one
> record?
>
> Is there another way to accomplish the same goal?
>
> B.

You are trying way too hard.

CREATE TABLE t (
RID INT DEFAULT 1,
STUFF INT); CREATE UNIQUE INDEX ix_t_one_rec
ON t(NVL2(rid, 1, 1));

PS: ID and DATA are both reserved words in Oracle.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org