Database Unique Key Columns

Hi,

I am looking for a best practice really...

I have a MS SQL table that contains a column that is unique, e.g. Name. In VB.NET what is the best practice to check that a new records Name value does not already exist in the database?

Do I:

1) try to catch the error in the insert query and if so how? OR

2) create a separate query that first validates the Name text to see if it already exists in the database and if it does then warn the user?

At the moment I am doing point 2.

Thanks, Greg
spen_langAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>I have a MS SQL table that contains a column that is unique, e.g. Name.
In case it's not obvious, name implies a character data type (char, varchar, ...) , which makes it a poor choice for a unique or primary key as the column will take more memory in bytes than numeric columns such as int, and you may get into problems if there are any collation, fat-finger typo issues, or if Bruce Jenner becomes Caitlin Jenner therefore your primary key just changed.

>In VB.NET what is the best practice to check that a new records Name value does not already exist in the database?
Can vary wildly based on what your company already has set up, but possibilities are to either run a query finding out if it exists, inserting without validation and creating an insert trigger on the table that enforces uniqueness, or create a stored procedure that performs the insert and handles duplicates in a more graceful manner.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.