3

I have a table 

documents
(
    year int not null, 
    number int not null, 
    document_types_id int not null, 
    ...
)

The primary key is year + number + document_types_id.

I think that a better primary key would be year + document_types_id + number. Is there a way to reorder this composition (not columns in table, PK and FK combination) without deleting and recreation of PK, because this PK is used as a FK in many other tables.

Thanks.

Vikdor
  • 23,934
  • 10
  • 61
  • 84
davor
  • 939
  • 2
  • 14
  • 31
  • Is this a hunch or have you tested that this primary key would perform better? – Robbie Dee Sep 05 '12 at 09:53
  • i'm not going to change PK and FK's on production databases. firstly, i would like to test performance of this combination. – davor Sep 05 '12 at 14:45
  • 1
    Presumably you have some scripts that'll test this rather than just seeing how it will fly. Perhaps try running an explain plan with an existing script, then putting in a unique index with the new column order. If the new index gives better performance you'll see this in the plan. Only then would I consider touching the primary key at all. Even then it may be worth leaving it in place depending on how many other scripts are likely to use it. – Robbie Dee Sep 05 '12 at 20:26

4 Answers4

1

You have to drop the primary key first to alter it later. Otherwise you get a message, that there can't be two primary keys on one table.

But that's no problem, just do

Alter Table myTable NOCHECK Constraint All

then alter your tables as you like, then do

Alter Table myTable CHECK Constraint ALL

and you're fine.

The equivalent in MySQL would be:

SET FOREIGN_KEY_CHECKS = 0;

and

SET FOREIGN_KEY_CHECKS = 1;
fancyPants
  • 50,732
  • 33
  • 89
  • 96
1

Your foreign keys are referencing your primary key, so your foreign keys are 3-dimensional (year + number + document_types_id). If you are going to get rid of a dimension then even if you try to modify your primary key your constraints will tell you that you can't get rid of the given column, so you should handle your foreign keys first and then you can modify your primary key. Steps:

  1. Write all your foreign keys into a list to enable you to know which were the foreign keys before.

  2. Get rid of all the foreign keys referencing your primary key

  3. Modify/recreate your primary key

  4. Recreate your foreign keys according to the new version of your primary key.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
0

If dropping the FK on other tables is a problem, then you can create a non-clustered index with those columns in that order and provide hints (WITH INDEX(ALTER TABLE syntax leaves no scope for an ALTER CONSTRAINT statement.

Vikdor
  • 23,934
  • 10
  • 61
  • 84
0

No. In the relational model, there is no meaningful ordering to the attributes in a key. But in SQL, there is. The correspondence of columns of a foreign key, to the columns of the primary or unique key the FK references, is by ordinal position, not by name.

So the ordering of the columns in the key declaration is meaningful, and if that meaning/ordering is currently used effectively, then you cannot change it without breaking the current use.

Besides. That the theory attaches no meaning to the ordering of the key attributes/columns, is not without reason. What exactly do you think is "better" with your "re-ordered" key, compared to the existing one ?

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • For me it's more natural to have types_id+number PK rather than number+types_id. Is this better? I don't know. I had this on mind: if we execute simple select statement with reordered PK we would get documents ordered by year, types_id and number. Then, if we need documents with certain types_id they are all in sequence (one after another) in result set. Using same select statement, if PK is year+number+types_id in result set we get records ordered by number and then types_id. For better understanding of my thoughts: http://sqlfiddle.com/#!3/eb843/8 – davor Sep 06 '12 at 09:37
  • You think wrong. If you execute simple select you get random ordering. You only get predictable ordering if you specify ORDER BY in the select. – Erwin Smout Sep 06 '12 at 15:04
  • Second thought : If you DO need some specific ordering, AND some index exists that has the column ordering in exactly the same order as your ORDER BY clause, then the system could be able to serve your request without re-sorting / re-arranging the rows. But you are still required to specify your desired ORDER BY explicitly. – Erwin Smout Sep 06 '12 at 15:07
  • how i get random ordering? if we look in 2nd select - result set is ordered by year asc, types_id asc and number asc (without order by). to be clear, i always specify order by clause, this is only for testing purposes. my idea was - if we extend our query and join tables on this pk (fk), and add this in query: WHERE types_id = 2 ORDER BY second_table_with_documents_FK.time_updated DESC, would the reordered PK (year+types_id+number) give us a better performance because select statement on table documents give us already sorted columns by types_id. – davor Sep 06 '12 at 16:44