RE: Journaling Constraints

<snip>
We have entertained the idea of creating another, unique identifier,
which
links all related files, for this database, but we will probably decide
against it. You see, in the project that we are working on, there are
four
applications, running on three servers (2 on PCs, 2 on iSeries), that
deal
with this data throughout the course of it's life. The Hospital account
number is the link between all servers. Creating another tracking number
for
these accounts, which will just be used internally to this database, and
is
only necessary for one function (history inquiry), seems unnecessary.
<snip>

Tony,

This is the exact situation such a field is intended to protect you
against. The rules (as I remember them) for a good primary key are:

1) They should be unique (if only one system is the master that data is
entered against, you can just use a next numbering system, if multiple
systems can enter new data, a GUID may be better, or, you may decide to
get all fancy and use something like a web service that dishes out
unique id's).
2) You should have control over the values used (an externally provided
account number or even a SSN violates this due to typos and accidental
duplication).
3) It should be meaningless outside of its role as a primary key.

This will not solve the problem of bad data coming in but it will solve
the problem of orphan records.

Matt