Re: Create Table question...

<alan_psb_at_yahoo.com> wrote in message news:835fq9$mv$1_at_nnrp1.deja.com...
> 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
> /
>
> 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?

Yes.

create table test (
  a number(3) constraint test_pk_a primary key

              using index tablespace ind_data, ....

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

> 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. Received on Wed Dec 15 1999 - 09:45:42 CST