469,087 Members | 1,298 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,087 developers. It's quick & easy.

Reorder Primary Key - any problem?

I need to reorder the primary key of a table. Say,

Primary Key (field1, field2)

changed to:

Primary Key (field2, field1)

Would like to know if this creates any problem to the
database?
Jul 19 '05 #1
4 4851

"t53864" <t5****@yahoo.com> wrote in message
news:9e**************************@posting.google.c om...
| I need to reorder the primary key of a table. Say,
|
| Primary Key (field1, field2)
|
| changed to:
|
| Primary Key (field2, field1)
|
| Would like to know if this creates any problem to the
| database?

sort of -- all FKs would be dropped when the PK is dropped, and then
recreated with the new column order explicitly indicated

do a little testing to see the effects

++ mcs
Jul 19 '05 #2
t53864 wrote:
I need to reorder the primary key of a table. Say,

Primary Key (field1, field2)

changed to:

Primary Key (field2, field1)

Would like to know if this creates any problem to the
database?


Primary key index would have different column ordering. Might impact
performance depending on your queries.
Jul 19 '05 #3
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message news:<T9********************@comcast.com>...
"t53864" <t5****@yahoo.com> wrote in message
news:9e**************************@posting.google.c om...
| I need to reorder the primary key of a table. Say,
|
| Primary Key (field1, field2)
|
| changed to:
|
| Primary Key (field2, field1)
|
| Would like to know if this creates any problem to the
| database?

sort of -- all FKs would be dropped when the PK is dropped, and then
recreated with the new column order explicitly indicated

do a little testing to see the effects

++ mcs


To clarify what Mark said if you attempt to drop a PK that is
referenced by a FK Oracle will return an error and refuse to perform
the drop unless you include the cascade constraints cluase. However,
it is your responsibility to re-create the dropped FK references.
Oracle does not automatically restore them in versions 7.0 - 9.2, and
I doubt that this has changed with 10g.

If there are no FK references to the PK then this is not a
consideration.

HTH -- Mark D Powell --
Jul 19 '05 #4
>[Case1] Primary Key (field1, field2)
index for (field1, field2) or (field2, field1) or *(field1)*.
[Case2] Primary Key (field2, field1)

index for (field1, field2) or (field2, field1) or *(field2)*.
so, If you query with condition (field1, field2) <- order doesn't matter
[Case1] and [Case2] should be the same, I think.

In case query with (field1) or (field2) only,
only [Case1] or [Case2] will be available.
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Dmitri Shvetsov | last post: by
3 posts views Thread by CodeRazor | last post: by
1 post views Thread by Mat | last post: by
2 posts views Thread by Asaf | last post: by
1 post views Thread by t53864 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.