checking to see if field value is unique

When entering a value into a SQL database is there a way to find out if that value has already been used in that field?

eg. If I were entering a last name into a field called "user_name" could I add some validation whereby the record couldn't be entered if someone else had already inserted that last name?You could:

1) use unique index in the user_name column to prevent duplicate values (i.e first name and last name into separate columns as well and target the index to the last name column). In this case DB throws an error if duplicate insert is tried.

2) you could also check the existence before inserting into table, something like:

IF (SELECT count(*) FROM MyTable WHERE Last_name='thename') > 0
BEGIN /*There is such last name already*/

END

ELSE /*There isn't such in the table*/

BEGIN

END|||Definitely create the unique contraint on the columns you don't want duplicated, as suggested. This will ensure that duplicate values cannot physically be entered.

You should also check for the existence before inserting into the table, so that you can gracefully capture the error and return user-friendly information back to the user. But, instead of the method suggested, you should try using EXISTS, which will stop processing as soon as the condition is met

IF EXISTS(SELECT NULL FROM MyTable WHERE Last_name='thename')
BEGIN /*There is such last name already*/
END
ELSE /*There isn't such in the table*/
BEGIN
END

Terri