FAQ
Hi,

What are different steps to be followed in modifying a primary
key(PK1) to exclude last primary key column from a list of primary key
columns.
assuming referential integrity constraints exists on primary key in diff
tables.
give necesary sql syntax to perform the tasks .

Thanks
Manoj

This e-Mail may contain proprietary and confidential information and is sent for the intended recipient(s) only.
If by an addressing or transmission error this mail has been misdirected to you, you are requested to delete this mail immediately.
You are also hereby notified that any use, any form of reproduction, dissemination, copying, disclosure, modification,
distribution and/or publication of this e-mail message, contents or its attachment other than by its intended recipient/s is strictly prohibited.

Visit Us at http://www.polaris.co.in

Search Discussions

  • Norman Dunbar at Oct 14, 2005 at 7:41 am
    10/14/05 10:13am >>>
    Hi,

    What are different steps to be followed in modifying a
    primary
    key(PK1) to exclude last primary key column from a list of primary key
    columns.
    assuming referential integrity constraints exists on primary key in
    diff
    tables.
    give necesary sql syntax to perform the tasks .

    Thanks
    Manoj

    Hi Manoj,

    you'd need to drop the foreign key constraints from *all* the child
    tables whihc reference the primary key you wish to change, then drop the
    primary key constraint from the table in question. Once you've done
    that, rebuild the primary key and then the foreign key's again.

    Something like :

    alter table child_1 drop constraint fk_1;
    alter table child_2 drop constraint fk_2;
    alter table child_3 drop constraint fk_3;

    alter table parent drop constraint pk_1;

    alter table parent add constraint pk_1 primary key (a,b,c);

    alter table child_1 add constraint fk_1 foreign key (x,y,z) references
    parent (a,b,c);

    and so on.

    Cheers,
    Norman.

    Norman Dunbar.
    Contract Oracle DBA.
    Rivers House, Leeds.

    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Jared Still at Oct 14, 2005 at 9:24 am
    >
    Hi,
    What are different steps to be followed in modifying a primary
    key(PK1) to exclude last primary key column from a list of primary key
    columns.
    assuming referential integrity constraints exists on primary key in diff
    tables.
    give necesary sql syntax to perform the tasks .
    All the syntax required is found right here:
    http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

    FYI: It is generally assumed that the poster of a question will have done a
    fair amount
    of research prior to asking a question requesting such a comprehensive
    answer.

    I see that Norman took the trouble to answer this. These types of questions
    often
    go unanswered if it appears that the poster did not try to answer the
    question on
    his own before asking someone else to answer it.

    BTW, this question is a perfect example of why surrogate keys should be
    used.
    The PK should never be modified.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • GovindanK at Oct 16, 2005 at 12:31 pm
    Manoj

    When you trim the PK, you would need to trim the FK too. Are you sure
    that this is what you need to do?

    hth
    GovindanK

    On Fri, 14 Oct 2005 15:22:00 +0100, "Jared Still"
    said:
    Hi,
    What are different steps to be followed in modifying a primary
    key(PK1) to exclude last primary key column from a list of primary key
    columns.
    assuming referential integrity constraints exists on primary key in diff
    tables.
    give necesary sql syntax to perform the tasks .
    All the syntax required is found right here:
    http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

    FYI: It is generally assumed that the poster of a question will have done
    a fair amount of research prior to asking a question requesting such a comprehensive
    answer.

    I see that Norman took the trouble to answer this. These types of
    questions often go unanswered if it appears that the poster did not try to answer the
    question on his own before asking someone else to answer it.

    BTW, this question is a perfect example of why surrogate keys should be
    used. The PK should never be modified.
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l
  • Mercadante, Thomas F (LABOR) at Oct 14, 2005 at 9:55 am
    I just didn't understand the question!



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared Still
    Sent: Friday, October 14, 2005 10:22 AM
    To: manoj.gurnani_at_polaris.co.in
    Cc: oracle-l_at_freelists.org
    Subject: Re: Modify Primary Key



    Hi,
    What are different steps to be followed in modifying a
    primary
    key(PK1) to exclude last primary key column from a list of
    primary key
    columns.
    assuming referential integrity constraints exists on primary
    key in diff
    tables.
    give necesary sql syntax to perform the tasks .

    All the syntax required is found right here:
    http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.
    htm

    FYI: It is generally assumed that the poster of a question will have
    done a fair amount
    of research prior to asking a question requesting such a comprehensive
    answer.

    I see that Norman took the trouble to answer this. These types of
    questions often
    go unanswered if it appears that the poster did not try to answer the
    question on
    his own before asking someone else to answer it.

    BTW, this question is a perfect example of why surrogate keys should be
    used.
    The PK should never be modified.



    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Yechiel Adar at Oct 16, 2005 at 7:45 am
    Assuming that you have n columns in the PK and the first n-1 columns are
    enough for PK.

    Why not leave this as it is?
    If you do not want to change everything at once you can create a
    unique index on the first n-1 columns and start to move the constraints
    to this index. When all is done just drop the PK constraint and you are
    done.
    All the fields should be already not null and the unique index on top of
    this gives you a PK.

    Adar Yechiel
    Rechovot, Israel

    manoj.gurnani_at_polaris.co.in wrote:


    Hi,
    What are different steps to be followed in modifying a primary
    key(PK1) to exclude last primary key column from a list of primary key
    columns.
    assuming referential integrity constraints exists on primary key in diff
    tables.
    give necesary sql syntax to perform the tasks .

    Thanks
    Manoj


    This e-Mail may contain proprietary and confidential information and is sent for the intended recipient(s) only.
    If by an addressing or transmission error this mail has been misdirected to you, you are requested to delete this mail immediately.
    You are also hereby notified that any use, any form of reproduction, dissemination, copying, disclosure, modification,
    distribution and/or publication of this e-mail message, contents or its attachment other than by its intended recipient/s is strictly prohibited.

    Visit Us at http://www.polaris.co.in
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 14, '05 at 4:18a
activeOct 16, '05 at 12:31p
posts6
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase