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

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.

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.

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.

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.

You guyes have hijacked the thread pretty effectively. The original question was: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

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 --here we have not, as you say, "enforced" an order, but the order is most certainly ~not~ an "artifact instead of an absolute"

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!

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.
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).
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."

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.