Re: Create Table question...

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

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

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

> 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. Received on Tue Dec 14 1999 - 08:44:57 CST