Re: limit TABLE to only one record

From: <fitzjarrell_at_cox.net>
Date: Wed, 14 Nov 2007 13:19:39 -0800
Message-ID: <1195075179.373953.145190@o38g2000hse.googlegroups.com>

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 Received on Wed Nov 14 2007 - 15:19:39 CST