DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: alter table add constraint

  1. #1
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Hi, all
    I know the syntax to create constraint,index with the initial table creation script with tablespace storage info. I also know how to alter table add constraint primary key with tablespace storage info. I can also alter table add constraint foreign key without specifying the tablespace.But I could not get right for the alter table add constraint foreign key with tablespace storage info. I would appreciate if someone can help me with the correct syntax and give me a working example. Thanks.
    The following is the foreign key I want to add. The snytax is correct, but I can not add tablespace storage into to it.
    ALTER TABLE "HUBADMIN"."STEALTH_NATIVEMSG"
    ADD CONSTRAINT "STEALTH_NATIVEMSG_FK" FOREIGN KEY( "DB20_ATOMICID", "DB20_HOPSTATUS")
    REFERENCES "HUBADMIN"."STEALTH_HDRS"("DB20_ATOMICID","DB20_HOPSTATUS");


  2. #2
    Join Date
    Oct 2001
    Posts
    126
    Oracle does not create storage space for foreign key so there is no need and no way to specify the tablespace information.

    Susan

  3. #3
    Oracle does not automatically create indexes on foreign keys. It looks to me like this is what you are looking for. If yes, then create manually your indexes on that foreign key, and there you can state any storage option you need.
    ovidius over!

  4. #4
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Thanks.

  5. #5
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Ovidius, that is what I am trying to do. It won't let me specify the tablespace it should go to. I just want to know if it is doable and how. Please show me an example if you do know the answer. Thanks.

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    create index in
    storage (initial 100k next 100k minextents 2 maxextents 100)
    tablespace index;
    anandkl

  7. #7
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Thanks, anandkl,
    I tried that, it did not work unless you show me one working example. Thanks.
    I tried many times, could not get it. I think srlhome00 is right that
    Oracle does not create storage space for foreign key so there is no need and no way to specify the tablespace information.

    can anybody confirm that?

  8. #8
    Join Date
    Oct 2001
    Posts
    126
    Let me elaborate it. Foreign key is used to check the referencial integrity. It is to confirm the data you entered is indeed match the rule with the parent tables. So, Oracle does not create any index for foreign key specified. That is, no storage created for foreign key generation.


    Susan

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