Re: limit TABLE to only one record

On Wed, 14 Nov 2007 13:19:39 -0800, "fitzjarrell_at_cox.net" <fitzjarrell_at_cox.net> wrote:

>On Nov 14, 12:55 pm, Brian Tkatch <N/A> 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.
>
>Your syntax, as mentioned earlier, is wrong. It should be:
>
>SQL> CREATE TABLE One_Record_Only
> 2 (
> 3 Id INT DEFAULT 1,
> 4 Data INT,
> 5 CONSTRAINT ORO1_Id_CK CHECK(Id = 1),
> 6 CONSTRAINT ORO1_Id_PK PRIMARY KEY(Id)
> 7 USING INDEX TABLESPACE INDX
> 8 );
>
>Table created.
>
>SQL>
>SQL> Insert into one_record_only (data)
> 2 values (1);
>
>1 row created.
>
>SQL>
>SQL> insert into one_record_only (data)
> 2 values (2);
>insert into one_record_only (data)
>*
>ERROR at line 1:
>ORA-00001: unique constraint (BING.ORO1_ID_PK) violated
>
>
>SQL> insert into one_record_only (id,data)
> 2 values (2,2);
>insert into one_record_only (id,data)
>*
>ERROR at line 1:
>ORA-02290: check constraint (BING.ORO1_ID_CK) violated
>
>
>SQL>
>
>
>
>David Fitzjarrell

I do not understand, why is it wrong? The code i posted executes without error.

The only difference i see is that mine also specifies the names of the NOT NULL CONSTRAINT and the name of the UNIQUE INDEX the PRIMARY KEY will use.

B. Received on Thu Nov 15 2007 - 07:54:38 CST