Re: Should I use PK or Unique Index in this table ?

"Alan" <alan_at_erols.com> wrote in message news:2rtoigF1e322lU1_at_uni-berlin.de...
|
| "xtanto" <krislioe_at_gmail.com> wrote in message
| news:e1c9bd55.0409272116.24fbae06_at_posting.google.com...
| > Hi gurus,
| >
| > I have a employee table, when an employee firstly input into the
| > system, he/she does not have Emp_Number yet, after some approval
| > process only then Emp_Number is generated.
| >
| > Question is : since Emp_Number can be null at first, should I have PK
| > on this table ? or just Unique Index ?
| >
| > Thank you,
| > xtanto
|
| Assign a unique temporary emp_number that you know will never be a valid
| emp_number. For examlpe, if you know that the values will always be
numeric,
| use an alpha in the data. But here's a thought- maybe they should not be
in
| the employee table until they are actually an employee?
|
|

a temporary PK is problematic --

first, it has intelligence (i am alpha, therefore i am temporary); not much intelligence, but some, which also probably does not match the actual business process (are temporary employee ids actually assigned?)

second, it requires an update, hence requires a cascade when the 'real' employee number is assigned

since a good candidate should be unique, non-updatable, and available, it looks like the employee number is not a good PK candidate. use a system-assigned PK and model the employee number as an optional UK

++ mcs Received on Tue Sep 28 2004 - 13:52:39 CDT