we have a table for storing transaction details where the combination of the following 3 columns need to be a unique composite value (nulls in any of the columns not allowed either, so composite could probably be a primary key):
location_code (single char like 'A', 'B', etc; currently, only 4 different values)
transaction_id (32 bit integer)
materialtype_id (32 bit integer, but currently numbers less than 10000 values)
will specifying the composite primary key or uniqueness constraint (during creation or definition time) in the following ways result in any significant differences in performance? in other words, does ordering of the columns matter, and if yes, why or how?
primary key(location_code, transaction_id, materialtype_id)
primary key(transaction_id, ...)
primary key(materialtype_id, ...)
...
currently, i'm thinking that when querying against each of those columns individually, filtering by location_id or transaction_id alone will occur somewhat more frequently than filtering by materialtype_id
thanks for any help!