>CREATE TABLE AcctOwnership
(SURROGATE_ID number PRIMARY KEY,
cust_nbr ..REFERENCES Customers,
acct_nbr ..REFERENCES Accounts,
..); <<
Get rid of the "surrogate_id" at once. A quote from Dr. Codd:
"..Database users may cause the system to generate or delete a
surrogate, but they have no control over its value, nor is its value
ever displayed to them ..."(Dr. Codd in ACM TODS, pp 409-410) and
Codd, E. (1979), Extending the database relational model to capture
more meaning. ACM Transactions on Database Systems, 4(4). pp.
397-434.
This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means
never used in queries, DRI or anything else that a user does.
Codd also wrote the following:
"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.
(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two
companies merge, the two employee databases might be combined with the
result that some or all of the serial numbers might be changed.).
(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.
(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it
has ceased to have one (e.g. and applicant for a job and a retiree).
These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] -
is to introduce entity domains which contain system-assigned
surrogates.
[emphasis begin] Database users may cause the system to generate or
delete a surrogate, but they have no control over its value, nor is
its value ever displayed to them....." (Codd in ACM TODS, pp 409-410)
[emphasis end].
That means if a magical created numbering (auto-increment, IDENTITY,
etc.) were a surrogate, we would not see its values, print them out as
invoice numbers, search on them, and so forth. That means if "magic
number" were a surrogate, we could drop it and the schema would still
work. Like an index. But "magic number" fails on both those
points.
>As to why the SURROGATE_ID is there... in reality, my issue has absolutely nothing to do with customers and accounts - it's for a completely different business application. I am describing it in terms of customers and accounts for the sake of straightforward communication. <<
It is very hard to answer a question where you have been told less
than nothing -- vague data is better than false data.
>The initial relationship table was not created to impose uniqueness on the cust_nbr/acct_nbr values (it's not a composite primary key as is the case in many relationship tables), but only to ensure that the referencing values exist in the referenced tables. <<
Unh? The referencing values MUST exist in the referenced table,
otherwise, they cannot be referenced!!! ARRRGHH!
>We now want to ensure that an account belongs to one and only one customer - but a customer can have one or more accounts. <<
I do a cute example in my books with wives and husbands in two tables,
which then are referenced by a monogamy table (UNIQUE wife, UNIQUE
husband), a polygamy table (UNIQUE wife, husband), polyandry table
(wife, UNIQUE husband) and an Orgy table (wife, husband). But it is
important not to have a magical key that will allow redundant
duplicate data.