How to create PK in the index tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to create PK in the index tablespace

  1. #1
    Join Date
    May 2001
    Posts
    285

    Question

    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!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    alter table xyz add constraint xyz_pk primary key (x) using index tablespace xyz_ts;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2001
    Posts
    285

    Thanks Jeff, but how about FK?

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

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131

    Lightbulb

    Does it need a STORAGE clause?
    Just a guess
    MH

  5. #5
    Join Date
    May 2001
    Posts
    285

    You are right...

    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?

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: You are right...

    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.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width