Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    370

    Question Unanswered: Position of columns in index creation.

    Hi all,
    Is order of column names in adding a foreing key constraint important? I mean is there any difference between:
    A) ALTER TABLE T2 ADD CONSTRAINT T2_TO_T1 FOREIGN KEY(ID2,NAME2) REFERENCING T1(ID1,NAME1)
    and
    B) ALTER TABLE T2 ADD CONSTRAINT T2_TO_T1 FOREIGN KEY(NAME2,ID2) REFERENCING T1(NAME1,ID1)

    I know from logical POV and relational model POV there is no difference but maybe implementation details have some magic for us

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    No. Both keys will always be used in the lookup anyway (unless you have an FK to a really weird unique key that allows nulls).

    btw your question and the post subject are about different things.

  3. #3
    Join Date
    May 2006
    Posts
    7
    Specific Ordering of columns for a constraint, at the logical level, are not an issue. At least that I am aware of.

    Now the fact is that many constraints also indicate the method, or columns that will be used in joining tables together.

    That being said, these constraints will typically be turned into indexes (by a DBA/tool/user) and thus the order should be considered as to how an index will be used in queries and how they will physically be stored within the data blocks.
    Last edited by Babboo.com; 05-13-06 at 15:43.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Babboo.com
    That being said, these constraints will typically be turned into indexes and thus the order should be considered as to how an index will be used in queries and how they will physically be stored within the data blocks.
    "Turned into"? In Oracle there is no automatic index creation for foreign keys.

    Oracle will create an index for a primary key automatically, but not for foreign keys. So the order in the FK constraint is completely unrelated to the ordering in the index.

    In the index definition the ordering indeed does matter.

    If you always use the two columns together, the order is not important. If there are situations where only one column is used in a WHERE clause then the ordering does matter.

  5. #5
    Join Date
    May 2006
    Posts
    7
    hm, never said that Oracle turned the FK into an index. Probably worded a bit funny there.

    All I meant was that since join criteria between tables, which is often times, not always, also shown through foreign keys, there is a good chance these FKs will be supported by index creations at some point down the line.

    Yes, it takes someone to physically tell oracle to create and index on the FKs.

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by Babboo.com
    That being said, these constraints will typically be turned into indexes (by a DBA/tool/user) and thus the order should be considered as to how an index will be used in queries and how they will physically be stored within the data blocks.
    Good point - specifying the constraint the wrong way around could fool someone into creating an index to match the constraint. Though of course in reality they would think carefully about it first and ensure that any index was optimized for the typical queries required by the application

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Babboo.com
    hm, never said that Oracle turned the FK into an index. Probably worded a bit funny there. All I meant was that since join criteria between tables, which is often times, not always, also shown through foreign keys, there is a good chance these FKs will be supported by index creations at some point down the line.
    I thought so as well, I just wanted to avoid misunderstandings.

    I do think we all agree on this point anyway

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •