Re: Oracle SQL - Update
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:
- 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.
- There could be an error resulting in duplicate numbers.
- People could give deliberately wrong numbers, resulting in duplicates.
- 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