Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    general rule for ordering foreign keys? (n00b)

    Is there a general rule for ordering foreign keys in a table? Does the order of foreign keys in a table matter at all?

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    What do you mean by "ordering foreign keys"? Are you referring to column order within a multi-column foreign key?

    SQL requires that the columns in a foreign key constraint must match in order the columns of a UNIQUE or PRIMARY KEY constraint. Many if not most DBMSs also require an index that matches the UNIQUE or PRIMARY KEY constraint.

    Indexes are usually more efficient if you put the most selective columns first in a multi-column index. By "most selective" I mean the column with the largest number of distinct values (aka the cardinality of the column).

    Selectivity isn't the only consideration for choosing the order of columns in an index. Another factor is whether those columns will actually be used in queries that access the data. An index may be more beneficial if the columns that are most often used in queries appear first in the index.

    Those are some very general guidelines for indexes but you should also research any specific advice for your particular DBMS.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    AFAIK foreign Keys have no impact on the order of data (nor does the primary key.....)

    ...except that keys are indexed and the query optimiser will make use of indexes as it sees fit to meet the requirements of the query. if you don't specify an order by clause then the recordset will be in the order of the index the optimiser used to filter the data. some sql engines default to the PK order.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by dportas
    Indexes are usually more efficient if you put the most selective columns first in a multi-column index. By "most selective" I mean the column with the largest number of distinct values (aka the cardinality of the column).
    Can you just clarify this for me?
    Given the example of a salary table where the primary key is a composite of employee_id and effective_date, which would you put first in the index? From what you stated I assume you'd have effective date as the first column in the index, but that doesn't feel right to me?

    George
    Home | Blog

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Can you just clarify this for me?
    Given the example of a salary table where the primary key is a composite of employee_id and effective_date, which would you put first in the index? From what you stated I assume you'd have effective date as the first column in the index, but that doesn't feel right to me?
    You'd put employee_id first because you're unlikely to include the effective_date as part of your search whereas employee_id is likely to be used in most queries. If you didn't have effective_date in your search then that index would never be used. You wouldn't really ever combine those particular fields into a composite index anyway.

    The order of fields in composite indexes is very important to get decent performance. If say you often did searches on your employees by name and sex and you wanted a composite index with these fields then (name,sex) would be better than (sex,name) because the optimiser has to make a choice in which index to use for any given query - if you start the index with the sex field then the optimiser will realise at best it will only reduce the search by 50% and so it will most likely disregard that index and just do a table scan. If the index was (name,sex) then even a common name like Smith will return a small subset of employees and so the index is worth using.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by georgev
    Can you just clarify this for me?
    Given the example of a salary table where the primary key is a composite of employee_id and effective_date, which would you put first in the index? From what you stated I assume you'd have effective date as the first column in the index, but that doesn't feel right to me?
    If the index is a B-Tree then putting the most selective column first should reduce the average index subtree cost, assuming all columns are being searched on. If it's a hash or a bitmap index I don't think it makes any difference.

    In your example I don't know which column would be more selective. In my experience salaries often change on the same date for lots of people so I wouldn't necessarily assume the date was more selective. As Mike says, the fact that employee_id may be used more often probably overrides other considerations about index efficiency.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You guyes have hijacked the thread pretty effectively. The original question was:
    Quote Originally Posted by rbfree
    Is there a general rule for ordering foreign keys in a table? Does the order of foreign keys in a table matter at all?
    My answer is there is no rule at all. There is no conceptual "order" of columns within a table... From the perspective of Relational Algebra, the table is a bag and the columns are jumble within the bag. Order is applied to result sets, not to tables.

    With that being said, every currently-commercially-successful database engine does in fact apply a default order to columns. That order can be helpful to Joe User. Apply that order as you see fit, however it benefits your situation. Just don't rely upon the order, and keep firmly in your mind that any order you don't enforce using either a column list or an ORDER BY clause is an artifact instead of an absolute.

    -PatP

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with one exception, pat, with one exception

    there ~is~ a specific column order to tables in a database -- the order in which the columns were declared

    now, this can be any order you wish -- it is common to see PKs declared first, but there is certainly no need for them to be

    this column order, and the fact that the database engine uses this column order, becomes immediately apparent in an INSERT statement when you fail to provide the values for the columns in the proper order --
    Code:
    INSERT
      INTO daTable
    VALUES
         ( 'valueforcol1' , 'valueforcol2' , ... )
    here we have not, as you say, "enforced" an order, but the order is most certainly ~not~ an "artifact instead of an absolute"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Pat Phelan
    My answer is there is no rule at all. There is no conceptual "order" of columns within a table... From the perspective of Relational Algebra, the table is a bag and the columns are jumble within the bag. Order is applied to result sets, not to tables.
    True. I did say "SQL" in my reply. SQL relies on logical column order in its FOREIGN KEY constraints (and in other places too: UNION, INSERT, MERGE, ...). So a constraint on (A,B) is not logically the same as (B,A) - contrary to common sense and the relational concept of a foreign key.

    In relational algebra both domain-ordered and domain-unordered versions of relations are sometimes used. Codd in his early papers asserted that attribute order was significant, although he later changed his mind. It is still quite common and respectable in relational theory to use both the ordered (unnamed) and unordered (named) perspectives interchangeably ordered notation being more concise. For actual implementations however, attribute names are clearly preferred!

  10. #10
    Join Date
    Feb 2009
    Posts
    104

    request some minor clarifications

    A clarification on my part: I wasn't descriptive enough in my inquiry; I was referring to ordering non-index foreign keys. However, reading Dave's post, I realized I needed to chew on the concept of index keys, which I've been doing. My grasp of how it actually works on a DBMS is a bit iffy, though the general concept is clear.

    Am I right in thinking that most DBMS automatically generate an index column for the primary key field on every table? (Access does.) Is there a logical relationship between the value of the index and the value of the key field, or is that relationship only physical (aside from contraints)? For instance, are the two values equal? Conversely, will each index key value in the entire database be unique?

    (If you can link me to a good article, please do. I've looked around but haven't found anything comprehensively useful.)

    This posting definitely rang the bell.
    Quote Originally Posted by Pat Phelan
    My answer is there is no rule at all. There is no conceptual "order" of columns within a table... From the perspective of Relational Algebra, the table is a bag and the columns are jumble within the bag. Order is applied to result sets, not to tables.

    With that being said, every currently-commercially-successful database engine does in fact apply a default order to columns. That order can be helpful to Joe User. Apply that order as you see fit, however it benefits your situation. Just don't rely upon the order, and keep firmly in your mind that any order you don't enforce using either a column list or an ORDER BY clause is an artifact instead of an absolute.

    -PatP
    However, from Rudy's and Dave's responses, I'm seeing that I need to be aware of my column order when writing an update query and formulating constraints.

    In general, I'm seeing a tension between the relational/set theory and the practice as well as a tension between machine efficiency and user needs (for instance, the trade-off between concise notation and the needs associated with implementation).
    Quote Originally Posted by dportas
    It is still quite common and respectable in relational theory to use both the ordered (unnamed) and unordered (named) perspectives interchangeably ordered notation being more concise. For actual implementations however, attribute names are clearly preferred!
    One of my favorite qtns, from Yogi Berra (almost as wise as Pogo): "In theory there is no difference between theory and practice. In practice there is."

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by rbfree
    Am I right in thinking that most DBMS automatically generate an index column for the primary key field on every table? (Access does.) Is there a logical relationship between the value of the index and the value of the key field, or is that relationship only physical (aside from contraints)? For instance, are the two values equal? Conversely, will each index key value in the entire database be unique?
    Most SQL DBMSs do create an index for PRIMARY KEY and UNIQUE constraints. An index is supposed to be a physical implementation feature only, not part of the logical level of the database. The internal structures used vary depending on which DBMS.

Posting Permissions

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