Maintaining Integrity

Question

Suppose I have two tables

1st table emp

EmpID  | EmpName|xyz...coloums

 1.    | Hrishi |
 2.    | Nikhil |
 3.    | Hrishi |

2nd Table Department

DeptId |EmpId |Deptname....Xyz coloum

 1.    |1    |computer

There is a one-to-many relation between emp and department table.

Now I want to call a stored procedure where empName will be an input. This stored procedure will insert empId in department table where empName = Hrishi. Of course, this is ambiguous.

Which hrishi will get selected from emp table 1st or 3rd?

How should I handle this senario?

Answer 1

This is going to cause issues.

When inserting a foreign key reference, you should always pass the foreign key value to the stored procedure...otherwise the data isn't guaranteed to be unique.

In your case, your stored procedure should take EmpId instead of EmpName. The calling code may have to modified to handle the stored procedure...but it's the only way to get the correct results every time.

Answer 2

Which order they are returned is undefined.

You basically should not have duplicates, so you should have a UNIQUE constraint on EmpName. either that, or write code to handle te situation you are describing.

Answer 3

There are a couple of options which depend upon your use case:

1. Enforce unique constraints on the employee name. Not a great solution, but something we commonly due in our data warehouse.

   CREATE UNIQUE NONCLUSTERED INDEX [ixu_employee_name] ON [emp] [EmpName] ASC )

2. Display both results to the user and allow them to pick the correct one. This could make sense in an intranet application. The underlying unique key of EmpID would simply represent a separate employee entity. If you are going to do this, then you should display multiple columns of information and possibly an employee image to make it easier for users to tell the difference between people.

Answer 4

The answer is that you should use an input to the stored procedure that is unambiguous, like the employee id, not the employee name. In a UI, you'd allow the user to choose a user by name, including enough information -- like email, office number, etc. -- to allow them to choose the correct one. Your program, however, would use the id of the selected employee when calling the stored procedure.

Answer 5

As a general rule, whenever you have a query that does this:

select <primary key> from <table> where <other column> = <value>

that implies that <other column> is unique. You need to create a unique constraint on Emp.EmpName and clean up the data to conform to that constraint. How you clean up the data depends on your business requirements.

Answer 6

The (one and only) purpose of the primary key is to uniquely identify a row in table. You should have only one place in a DB, or an application, where primary keys are generated. The simplest way is to use an AUTO-INCREMENT field.

If your follow this and put this example in 2NF your question will solve itself.

• Remove EmpID from the Department table, it does not belong there.

• Place DeptID into Employee table.