0

I want to have EmpId and userid(loginid) in one table. What's best way to do? can i go for two primary keys as both of these fields can not have duplicate values in the table?

Moin Shaikh
  • 376
  • 3
  • 15
  • 2
    You can only have one primary key, but you can have as many unique indexes/constraints as you like. Decide which one (if any) makes more sense to be your primary key, make this your primary key, then use a unique constraint or a unique index to ensure uniqueness of the other. Although if they are both unique do you really need to store the relationship in another table? i.e. `loginid` could just be another field in the employee table, or vice versa. – GarethD Jun 02 '14 at 10:31
  • Thanks, so i can just set empid as p_key and can have constraints for username field, right? Thanks. Would use empid as pkey and then a unique constraint for userid. and no there isn't such need of establishing foreign relationship. – Moin Shaikh Jun 02 '14 at 10:35
  • Use a composite primary key, please see http://stackoverflow.com/questions/217945/can-i-have-multiple-primary-keys-in-a-single-table – 3dd Jun 02 '14 at 10:36
  • @3dd A composite primary key would not ensure uniqueness of both fields, i.e. `(1, 1), (1, 2)` would not violate the primary key, but in the first of the two column 1 has been duplicated. @Moin Shaikh Yes, a unique index on `loginid` would work, this would eliminate the need for a foreign key relationship. Also means you could allow `NULL` values in `loginid` for historic employees if you set up a [unique filtered index](http://msdn.microsoft.com/en-gb/library/cc280372.aspx) – GarethD Jun 02 '14 at 10:49

0 Answers0