Re: limit TABLE to only one record

On Thu, 15 Nov 2007 08:54:38 -0500, Brian Tkatch <N/A> wrote:

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

<SNIP>
>>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 );
>>

<SNIP>
>>David Fitzjarrell

<SNIP>

OK, i see, i did not realize Oracle named the PK INDEX withe the same name as the CONSTRAINT. I remember using USING INDEX, but i must have used it for choosing the TABLESPACEs, and not for naming.

I don't understand the NOT NULL issue though. The documentation states:

Oracle enforces all PRIMARY KEY constraints using indexes. In Figure 21-5, the primary key constraint created for the department_id column is enforced by the implicit creation of:

• A unique index on that column
• A NOT NULL constraint for that column

So, I tried an example:

SQL>CREATE TABLE A(A INT CONSTRAINT A PRIMARY KEY); Table created.

SQL>SELECT

  2     Constraint_Type,
  3     Constraint_Name
  4  FROM
  5     DBA_Constraints
  6  WHERE
  7     Table_Name = 'A';

C CONSTRAINT_NAME

- ------------------------------

Where is the NOT NULL CONSTRAINT?

B. Received on Thu Nov 15 2007 - 08:57:02 CST