How to create PK in the index tablespace

[elaine3839]

I'd like to add Primary Keys to the newly created tables and also make sure the PKs are created in the index tablespace instead of the default one.

Can I achieve this 2 requirements in 1 sql statement?

I tried

Alter table xyz add constraint PK_xyz PRIMARY KEY (col_a)tablespace idx_tbs;

But the tablespace part is causing error. How should I modify it or I have to do it in a 2-step way?

Thanks!

[marist89]

alter table xyz add constraint xyz_pk primary key (x) using index tablespace xyz_ts;

[elaine3839]

The following syntax didn't work

ALTER TABLE xyz ADD CONSTRAINT FK_xyz_opq FOREIGN KEY (x) REFERENCES opq USING INDEX TABLESPACE xyz_ts;

Thanks again...

[Mr.Hanky]

Does it need a STORAGE clause?
Just a guess
MH

[elaine3839]

Just realized that no index will be created based on FK, so I probably don't need to worry about storage at all...

Just curious -- how does Oracle save the FK?

[jmodic]

Originally posted by elaine3839
Just curious -- how does Oracle save the FK?

Oracle implements PK and UK constraints by underlying indexes, so if there is no appropriate index allready created Oracle creates one automaticaly. But for FK constraints it doesn't realy need an underlying index, as the FK constraint is referencing a foriegn table. In fact, FK is actualy referencing a PK or UK constraint on the referencing table, and those PK or UK allready have an underlying index....

In short, Oracle implements FK by checking a remote PK or UK index to see if the current row has a maching value in a referencing table - so it realy doesn't need an index on a local table. But under certain circumstances there is a good habit to index FK constraints nevertheless manually.....