I have a composite primary key for a certain table which is a composite of 3
of its columns. I also store a foreign key to this primary key in another
table.

Is there any way to refer to this composite key as one when performing a
join?

Thanks, Alex

In article <c1lheq$185$1 (AT) titan (DOT) btinternet.com>, Alexander Brown wrote:


Is your problem that you find it cumbersome to combine the parts of the
composite key each time you write a join command?

If that is the case, I wonder would it be wise to have a field of its
own for the composite key, where into you forge the contents of its
parts? Then in the join command you could subsequently use the
ready-made key value.

In this scenario the parts of the composite key would remain each in its
own field, but the contents would be incorporated in the key field. In
this manner you would need to combine the parts of the composite key
only once (each time you save the record).

The benefit would be that each join command would be easy. The drawback
would be that the composite key would lose its natural connection with
the parts that it is made up from. Also the forged key field would
repeat the values of existing fields.

This is a suggestion that I am very unsure of. Therefore I would like to
hear your criticism on it.

jp
--

"Alexander Brown" <NoSpamPleasePummel (AT) Hotmail (DOT) com> wrote:
key. Either solution introduces potential problems however.

In SQL-92, use a row constructor: (T1.a, T1.b, T1.c) = (T2.a, T2.b, T2.c)

In most SQL products, however, you have AND three equality tests.

"Alexander Brown" <NoSpamPleasePummel (AT) Hotmail (DOT) com> wrote

Don't think so, but some DBMS products support predicates on the form:

(t1.x, t1.y, t1.z) = (t2.x, t2.y, t2.z)

Easier than typing separate predicates.


DG

"Jussi Piekkala" <piekkala (AT) removethis (DOT) cc.helsinki.fi> wrote

You would need to enforce another arbitrary rule. Much better to create an
arbitrary simple key instead.

Alex,

If this is an "transactional" (grin) database with lots of inserts,
updates, deletes, then I'd recommend using a SID (Sequential
Identifier). Many databases support the ability to autoincrement a
sequential number in a column ("the SID") typically given a starting
seed. The SID would be an additional column associated with your 3
"natural" columns in the same row/tuple. You can then replace the 3
"natural" columns with the SID as the foreign key in the dependent
table.

This can provide at least 4 potential benefits:

1. If your 3 columns contains character or textual values, the SID
could be many times more efficient, exponentially so in high row count
tables.
2. Using a SID in a transactional system also allows the maintenance
"application" to make changes to the 3 natural columns without causing
the additional database overhead to cascade the change to any/all
tables with that foreign key. (Aside...I use the phrase "application"
above because it seems that while database referential integrity (RI)
management functionality is built-in to all commercial database
engines, quite often RI is still maintained by an application external
to the database.)
3. Partitioning the dataset (if needed) becomes very straightforward
and fine-grained.
4. Sorting by the primary key can be much more efficient using numeric
values vs. character resulting faster extracts by PK and/or a more
efficient PK index structure.

A couple of trade-offs are:
1. Increased datastorage (= $) to contain an element that adds no
business value to the system of record.
2. The need to make allowances for collisions when merging tables
using SIDS separately assigned, but then this problem would exist with
merging separately managed PK's of any kind.

So all in all using a SID for a transactional database isn't such a
bad thing.

Using a SID in a data warehousing situation has it's own issues.

Later.

"Dataman" <dataman (AT) ev1 (DOT) net> wrote

That's an awfully long way of saying that the criteria for key selection are
simplicity, stability, familiarity and irreducibility.

"D.McMunn" <dmcmunn (AT) yahoo (DOT) com> wrote