Re: limit TABLE to only one record

On Sun, 18 Nov 2007 23:16:24 -0800, DA Morgan <damorgan_at_psoug.org> wrote:

>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.

I am trying to do it correctly, and learn if i am making incorrect assumptions. I appreciate the help. I also like it when the CONSTRAINT error is intutive, which means naming the individual CONSTRAINTs consistently.

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

Why would you make the UNIQUE INDEX and not just specify a PRIMARY KEY (which CREATEs it's own UNIQUE INDEX to enforce the rule).

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

Where? I know DATA is used in SQL*Loader. On second thought, "datum" would probably be more appropriate anyway. :P

Where is ID a reserved word? I have always defaulted to Id as the PK on a TABLE that defines an entity in the system.

B. Received on Mon Nov 19 2007 - 08:52:27 CST