indexig the right way

[victorashi]

hello , thinking of normalising my db i wanted to index the name column so that there is no duplicate possible .
this is the index that i made :
ALTER TABLE [dbo].

WITH NOCHECK ADD
CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED
(
[usr_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].

ADD
CONSTRAINT [IX_table] UNIQUE NONCLUSTERED
(
[name_of_employee]
) ON [PRIMARY]
GO
if i want to insert a new employee it runs OK , but if i want to see if it does not allow duplicates names of employee , it gives me the next errors :

Server: Msg 2627, Level 14, State 2, Procedure introducing_employee, Line 32
Violation of UNIQUE KEY constraint 'IX_table'. Cannot insert duplicate key in object 'table'.
The statement has been terminated.
Server: Msg 515, Level 16, State 2, Procedure introducing_employee, Line 53
Cannot insert the value NULL into column 'usr_id', table '1212.dbo.viza'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Server: Msg 515, Level 16, State 2, Procedure introducing_employee, Line 56
Cannot insert the value NULL into column 'usr_id', table '1212.dbo.work_permit'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Server: Msg 515, Level 16, State 2, Procedure introducing_employee, Line 59
Cannot insert the value NULL into column 'usr_id', table '1212.dbo.passport'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Stored Procedure: 1212.dbo.introducing_employee
Return Code = -6

is this normal ?

 

[jbenson001]

Yes this is normal. The last 2 errors are probably because of the first 2 errors that don't allow the insert.

 

[victorashi]

you are right, but in the end i have to connect this to a web page made in VS2005 , and there i would want to to give me a message like ' the employee is already in the DB' , and this concerns me more, because it will give me an unexpected error .

 

[jbenson001]

What I do in the page is to do a count based on the key. Lets say for example the key is social security number. You can do something like this:

'Get the SSN the user entered.
Select Count(*) From YourTable Where SSN = SSNEnteredByUser
If the Count > 0 Then
'Display an Error Message
Else
'Insert the new row
End If

Jim