Reorder Primary Key - any problem?

#1

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?

#2

"t53864" <t53864 (AT) yahoo (DOT) com> wrote

Quote:

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

#3

t53864 wrote:

Quote:

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.

#4

"Mark C. Stock" <mcstockX@Xenquery .com> wrote

Quote:

"t53864" <t53864 (AT) yahoo (DOT) com> wrote in message
news:9e76ab37.0409020505.501f06d7 (AT) posting (DOT) google.com...
| 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 --

#5

Quote:

[Case1] Primary Key (field1, field2)
index for (field1, field2) or (field2, field1) or *(field1)*.

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Reorder Primary Key - any problem?

comp.databases.oracle comp.databases.oracle

Reorder Primary Key - any problem? - 09-02-2004 , 08:05 AM

Re: Reorder Primary Key - any problem? - 09-02-2004 , 08:29 AM

Re: Reorder Primary Key - any problem? - 09-02-2004 , 09:47 AM

Re: Reorder Primary Key - any problem? - 09-03-2004 , 08:25 AM

Re: Reorder Primary Key - any problem? - 09-06-2004 , 09:34 PM