Very Computer

Basic questions about creating a table with constraints.  Answers
intermixed with questions:

See the constraint clause in the SQL manual for additional parameters:
  using index tablespace idx_space

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

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

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

Quote:> Thanks,
> Alan

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

Sent via Deja.com http://www.deja.com/
Before you buy.

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

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

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

Quote:> >    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;

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

Yes.

create table test (
  a number(3) constraint test_pk_a primary key
              using index tablespace ind_data, ....

Quote:>    ii) Apart from the primary key constraint, did other constraints
> (e.g. foreign key and NOT NULL) also use tablespace to store the
> information?

Unique constraints.

Quote:>    iii) After the above SQL statement has been processed, then issue the
> 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?

create table test3 (a primary key using index tablespace ind_data, b, c, d)
  tablespace testing3
  as select * from test;

Note that you can not specify the column datatypes.