Re: primary keys and tablespaces

One could suggest reading the documentation, since this particular issue is extremely *well* documented.

However, the basic syntax is 'alter table blah add primary key xxx USING INDEX
... tablespace yuck';

In other words, it's not the constraint that gets housed in a tablespace, but the INDEX that's associated with the constraint (same holds for unique constraints).

Here's an example of creating and enabling the constraints at the time of creating the table. Came from a post here last night, is not exactly what you're after, but the syntax remains the same:

CREATE TABLE blah(
cod CHAR(1) CONSTRAINT pk_estano_cod PRIMARY KEY, dsc VARCHAR2(45) CONSTRAINT nn_estano_dsc NOT NULL, abr VARCHAR2(3) CONSTRAINT nn_estano_abr NOT NULL CONSTRAINT uq_estano_abr UNIQUE
)
PCTFREE 5
PCTUSED 90
TABLESPACE USERS
STORAGE(INITIAL 1K
NEXT 1K
MINEXTENTS 1
MAXEXTENTS 121
PCTINCREASE 0) ENABLE CONSTRAINT pk_estano_cod USING INDEX TABLESPACE INDEX
STORAGE(INITIAL 1K
NEXT 1K
MINEXTENTS 1
MAXEXTENTS 121
PCTINCREASE 0)
PCTFREE 5 ENABLE CONSTRAINT uq_estano_abr USING INDEX TABLESPACE INDEX
STORAGE(INITIAL 1K
NEXT 1K
MINEXTENTS 1
MAXEXTENTS 121
PCTINCREASE 0)
PCTFREE 5
;

Regards
HJR <csykora_at_my-deja.com> wrote in message news:94v3jd$4a2$1_at_nnrp1.deja.com...
> I am creating a table without any contraints in
> the create table sql. I then want to execute an
> alter table statement to add the primary
> key...well the problem is in dictating what
> tablespace the primary key contraint gets created
> in. I need to do something like this:
>
> create table foo (test char(2), oid number(38));
>
> then execute something like:
>
> alter table foo add primary key(oid) tablespace
> mytablespace;
>
> because the primary key needs to be created in a
> tablespace other than the default for a
> particular schema...but i can't get the alter
> table command to point to the correct
> tablespace...any ideas on the syntax for the
> alter table statement?
>
> Thanks in advance,
>
> Chad
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Sat Jan 27 2001 - 17:41:33 CST