Re: Changing Tablespace of Primary + Foreign key indexes

From: Mark Powell <Mark.Powell_at_eds.com>
Date: 20 May 1999 19:59:04 GMT
Message-ID: <01bea2fb$4dbd5fa0$a12c6394@J00679271.ddc.eds.com>

You will have to drop and re-create the constraints. When you create a constraint you should specify the tablespace to be used to hold the index. You can also build the indexes first then define the constraints and Oracle will use the existing indexes to enforce the PK and UK constraints. Specifying a FK does not create an index; it requires that a PK or UK constraint already exists for the referenced columns.

For syntax see the SQL manual, constraint clause. Here is one method: alter table owner.table_name drop primary key; alter table owner.table_name
add constraint TABLE_NAME_PK
  primary key (key_column)
  using index
  tablespace IDXSPC
  pctfree 05
  maxtrans 8
  initrans 4
   storage (initial 32k

            next 32k
            maxextents 32
            pctincrease 0
            freelists 1

);

Matt Randle <matt_at_imat.demon.co.uk> wrote in article <927211691.21428.0.nnrp-02.d4e48d0d_at_news.demon.co.uk>...
> Can anyone tell me how to change the tablespace of the Primary + Foreign
key
> indexes created by Oracle when you specify PK + FK constraints ??
>
> Thanks,
>
> Matt.
Received on Thu May 20 1999 - 14:59:04 CDT