Re: Oracle SQL - Update

From: Michael Nolan <nolan_at_helios.unl.edu>
Date: 16 Sep 1994 14:40:53 GMT
Message-ID: <35capl$scg_at_crcnis1.unl.edu>


cudau_at_csv.warwick.ac.uk (Hank Robinson) writes:

:I presume that the column ssn, in both tables, is the primary key; if not, then
:there is the danger that you might have Two records in the ps_employee_update
:table with the same ssn, in which case the update statement above would fail
:(you'd be trying to update a single record from the ps_personal_data table
:with Two records from the ps_employee_update table).

This isn't really related to the initial problem but raises a design issue. Relying on the social security number as a GUARANTEED unique key is not a good idea, either, for at least 4 reasons:

  1. There were some duplicate numbers issued. (None lately, I believe.) I actually HAD a situation about 20 years ago in which we ran into a legitimate duplication.
  2. There could be an error resulting in duplicate numbers.
  3. People could give deliberately wrong numbers, resulting in duplicates.
  4. There could be good reasons why an employee could have a duplicate SSN, for example a rehired employee for which merging of 'old' and 'new' data is not desirable.

Reasons 2 and 3 are fairly similar from the data entry point of view (but not from the standpoint of manual procedures and policies), and can be gotten around with proper design. Reasons 1 and 4 may not be so easy.

The FAQ on social security numbers covers some of the sociological issues.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_notes.tssi.com, dbms_at_genie.geis.com
(posted from nolan_at_helios.unl.edu)
Received on Fri Sep 16 1994 - 16:40:53 CEST

Original text of this message