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

"Jim Smith" <jim_at_jimsmith.demon.co.uk> escribió en el mensaje news:GVhHRHL8XSWBFwOj_at_jimsmith.demon.co.uk...
> In message <4159068a.423093_at_news.inet.tele.dk>,
> KennethKoenraadt@?.?.invalid writes
> >On 27 Sep 2004 22:16:51 -0700, krislioe_at_gmail.com (xtanto) wrote:
> >
> >>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
> >
> >Hi Xtanto,
> >
> >Primary Key = Unique + Not Null ===>> Primary key constraint on
> >Emp_number not possible.
> >
> But should probably use a sequence to give you a surrogate key and put a
> PK constraint on that as well.
> --
> Jim Smith
> Because of their persistent net abuse, I ignore mail from
> these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
> For an explanation see <http://www.jimsmith.demon.co.uk/spam>

Hi Xanto,
A possible solution is to split the table in two: - Emp table
- Person table
In this case you can first enter the future employee's details in the person table (perhaps using a sequential PK as Jim Smith suggests.) Once the approval process is complete, you can introduce the Emp_number (+other related details?) into the emp table, linking on the sequential PK.

hth
Anthony Hogan Received on Tue Sep 28 2004 - 04:33:02 CDT