Re: Create Table question...

From: <why317_at_my-deja.com>
Date: Tue, 14 Dec 1999 22:45:02 GMT
Message-ID: <836h9d$r0h$1@nnrp1.deja.com>

In article <835l57$4ka$1_at_nnrp1.deja.com>,   markp7832_at_my-deja.com wrote:
> Basic questions about creating a table with constraints. Answers
> intermixed with questions:
>
> In article <835fq9$mv$1_at_nnrp1.deja.com>,
> alan_psb_at_yahoo.com wrote:
> > I created the following table: (Ver 7.3.3)
> >
> > CREATE TABLE test (
> > A NUMBER(3) CONSTRAINT test_pk_a PRIMARY KEY,
> > B NUMBER(4) CONSTRAINT test_fk_b REFERENCES test2,
> > C DATE CONSTRAINT test_nn_date NOT NULL,
> > D NUMBER(3))
> > TABLESPACE testing
> > /
> >

Because I like to have a little more control over sizing parameters, I would use something more like this:

  create table TEST (

      A number(3),
      B number(4),
      C date
        constraint TEST_NN_1 not null,
      D number(3),
      constraint TEST_PK
        primary key ( A )
        using index
          tablespace TESTING_X
          storage ( pctincrease 0 ),
      constraint TEST_FK_1
        foreign key ( B )
        references TEST2 ( B ))

    tablespace TESTING
    storage ( pctincrease 0 );

> > i) Is it possible to specify the tablespace of the primary
> > key on the above DDL in Oracle? Or it must use another
> > statement to change the index's (i.e. primary key)
> > tablespace?
> >
> See the constraint clause in the SQL manual for additional
> parameters:
> using index tablespace idx_space

If I remember 7.3.3, these additional parameters may only work in the TABLE_CONSTRAINTS area, not under the COLUMN_CONSTRAINTS format.

> Once allocated you can not relocate a primary key's supporting
> index without dropping and recreating it.

Unless you rebuild it in another tablespace...

  alter index TEST_PK rebuild tablespace TESTING_X;

...but you still won't be able to alter your INITIAL_EXTENT  allocation.

> > ii) Apart from the primary key constraint, did other
> > constraints (e.g. foreign key and NOT NULL) also use
> > tablespace to store the information?
> >
> The entire table, and with your code its primary key index, are
> stored in the tablespace testing. Not null and FK constaints are
> definitions in the dictionary and take no table/index data storage
> area. They do take a small amount of room in the dictionary base
> tables which reside in the system tablespace.
>
> > iii) After the above SQL statement has been processed, then
> > issue following SQL statement:
> >
> > create table test3 tablespace testing3 as select * from test;
> >
> > How do I specify the primary key (and its tablespace) of
> > table test3 by using the above SQL statement?
>
> You don't. You have to create it in a separate statement using the
> alter table add constraint clause. See the constaint clause entry in
> the SQL manual.

If you want control over the tablespace of the index and other parameters, I recommend creating the table first, then copying the data with a separate SQL statement...

  insert into TEST3
    select * from TEST;

> > Thanks,
> > Alan
> >
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --

Bill

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 14 1999 - 16:45:02 CST