Re: primary keys and tablespaces

Or do it all in one command:

Create Table LDGLOBALINFO.LG_COMMODITY_DELIV_TERMS_LKUP ( COMMODITY_DELIVTERMS_KEY NUMBER(8) NOT NULL,   constraint COMMODITY_DELIVTERMS_PK primary key (COMMODITY_DELIVTERMS_KEY)   using index tablespace LDGLOBALINFO_INDX   storage (initial 32K next 32K pctincrease 0 minextents 1 maxextents 249) )
tablespace LDGLOBALINFO
storage (initial 32K next 32K pctincrease 0 minextents 1 maxextents 249) ;

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3a735c7c$1_at_news.iprimus.com.au...
> 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 - 19:08:46 CST