SQL Insert Prevent Duplicates

Hello Experts,

Could someone please review my stored procedure below. I want to make sure that if a user try to enter a duplicate entry for the following fields below that he/she will not be able to.

I want to make sure that if a user enters a value for either of the following parameters below that the insert fails.

users_flname
users_email
users_username
ALTER PROCEDURE [dbo].[HealthCourses_InsertUsers]

(
@c_id int,
@bldg_id int,
@s_id int,
@users_flname varchar(50),
@users_street_address varchar(50),
@users_city varchar(50),
@users_zip varchar(5),
@users_phone varchar(12),
@users_work_ext varchar(4),
@users_email varchar(100),
@users_username varchar(50),
@users_password varchar(50),
@users_password_salt varchar(50)
)

AS

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM  dbo.HealthCourses_Users WHERE users_flname = @users_flname AND users_email = @users_email AND users_username = @users_username)
BEGIN
   INSERT dbo.HealthCourses_Users (c_id, bldg_id, s_id, users_flname, users_street_address, users_city, users_zip, users_phone, users_work_ext, users_email, users_username, users_password, users_password_salt)
   VALUES (@c_id, @bldg_id, @s_id, @users_flname, @users_street_address, @users_city, @users_zip, @users_phone, @users_work_ext, @users_email, @users_username, @users_password, @users_password_salt) 
COMMIT;

END

ELSE

BEGIN
    RAISERROR('You are already in the system!', 16, 1)  
 ROLLBACK TRAN 
 
END

Open in new window

LVL 4
asp_net2Asked:
Who is Participating?
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.

 
sammySeltzerCommented:
I personally thing that you need only email and username.

I am assuming that users_flname means first name.

If so, that won't work as you will run into several people with same first names.

Other than that, I think you are fine.
0
 
adamnlCommented:
You can use the SQL keyword 'UNIQUE' for this.
0
 
sdstuberCommented:
constraints should be declarative rather than procedural


ALTER TABLE dbo.HealthCourses_Users ADD CONSTRAINT uc_HealthCourses UNIQUE (users_flname, users_email , users_username)
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
asp_net2Author Commented:
@sammySeltzer,

users_flname is First and Last Names combined. If you believe that I only need to check against email and username then what I have is that okay or do I need to use AND & OR?
0
 
sdstuberCommented:
if you add the unique constraint on the table you don't need to do the select query, it'll be faster to simply do the insert and capture the error if the constraint blocks it.

by using a constraint, your data will maintain integrity even if it's modified by some means OTHER than your procedure
0

Experts Exchange Solution brought to you by ConnectWise

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
 
asp_net2Author Commented:
@sdstuber,

Can I have both methods? Also, how do I create a CONSTRAINT like you are suggesting?
0
 
sdstuberCommented:
yes you "can" but why would you want to?  The constraint eliminates the need for the additional select.

you would create it with the statement above  http:#37018195


you can change the name, of course
0
 
asp_net2Author Commented:
Is this the preferred way to handle stuff like this is to use Contraints rather than checking using Stored Procedures? I'm only asking because I'm still learning SQL Server.

What are the PROS and CONS for each method (Constraints) vs (Stored Procedures) for this?
0
 
sdstuberCommented:
as noted above,  anything that can be enforced "declaratively" is generally better.

the sql server engine can check for uniqueness (or other constraint conditions) faster than you can with a select statement.  

declared constraints are always in effect.

if I simply do "INSERT dbo.HealthCourses_Users (c_id, bldg_id, s_id, users_flname, users_street_address, users_city, users_zip, users_phone, users_work_ext, users_email, users_username, users_password, users_password_salt)
   VALUES (@c_id, @bldg_id, @s_id, @users_flname, @users_street_address, @users_city, @users_zip, @users_phone, @users_work_ext, @users_email, @users_username, @users_password, @users_password_salt)"

then your procedure won't work because I didn't invoke your procedure.

similarly  if I update the table I could create duplicates and your procedure wouldn't be able to prevent that either.


if you block all direct access to the tables and only use procedures then you need to add the check to every one of your routines.


if you declare a table constraint  it's always in effect, regardless of who/how the table is modified.


0
 
sammySeltzerCommented:
either way should work for you.

It is up to you which one to use.

That's my opinion anyway.
0
 
sdstuberCommented:
the only benefit I can think of to a procedural rule  is if the rule should NOT be enforced all the time.

if there is something special about your procedure that it should not create duplicates but it's ok if everything else does, then a procedural rule seems appropriate.
otherwise, it's just more code and not as robust as a declarative constraint
0
 
asp_net2Author Commented:
@sdstuber,

If I use the Constraint method then what should my Stored Procedure look like then? Can you modify it for me if I implement Contraints on those two or three fields?
0
 
sdstuberCommented:
BEGIN TRANSACTION;
BEGIN TRY
   INSERT dbo.HealthCourses_Users (c_id, bldg_id, s_id, users_flname, users_street_address, users_city, users_zip, users_phone, users_work_ext, users_email, users_username, users_password, users_password_salt)
   VALUES (@c_id, @bldg_id, @s_id, @users_flname, @users_street_address, @users_city, @users_zip, @users_phone, @users_work_ext, @users_email, @users_username, @users_password, @users_password_salt)
   COMMIT;
END TRY
BEGIN CATCH
   RAISERROR('You are already in the system!', 16, 1)  
   rollback tran
END CATCH
0
 
asp_net2Author Commented:
@sdstuber,

Ok, once again only asking because I don't know :) If I where to use Contraints then is this how my stored procedure would look? using transactions to check agains contraints and if a duplicate would occur then BEGIN CATCH would fire?
0
 
sdstuberCommented:
you start the transaction like you did originally,  the try/catch  replaces your select/if/else
0
 
sdstuberCommented:
something like this...


ALTER PROCEDURE [dbo].[HealthCourses_InsertUsers]

(
@c_id int,
@bldg_id int,
@s_id int,
@users_flname varchar(50),
@users_street_address varchar(50),
@users_city varchar(50),
@users_zip varchar(5),
@users_phone varchar(12),
@users_work_ext varchar(4),
@users_email varchar(100),
@users_username varchar(50),
@users_password varchar(50),
@users_password_salt varchar(50)
)

AS

BEGIN TRANSACTION;
BEGIN TRY
   INSERT dbo.HealthCourses_Users (c_id, bldg_id, s_id, users_flname, users_street_address, users_city, users_zip, users_phone, users_work_ext, users_email, users_username, users_password, users_password_salt)
   VALUES (@c_id, @bldg_id, @s_id, @users_flname, @users_street_address, @users_city, @users_zip, @users_phone, @users_work_ext, @users_email, @users_username, @users_password, @users_password_salt)
   COMMIT;
END TRY
BEGIN CATCH
   RAISERROR('You are already in the system!', 16, 1)  
   rollback tran
END CATCH
0
 
asp_net2Author Commented:
@sdstuber,

Ok, last question. Is there a way that I can create Contraints on multiple fields in my Table using SQL Server Management Studio and if so how? Also, should I always use the contraint for Insert and Updates?
0
 
sdstuberCommented:
you can right click on "Constraints" for a table and click New Constraint,you can add whatever expression you want into the constraint area.

You can't create 2 different constraints on different columns at the same time though
0
 
sdstuberCommented:
as for "always for insert and update"  - probably yes.

however, it's really a business requirement.  if there is a reason to allow duplicates (or other constraint violations) then don't enforce it always (maybe look at the procedural method again)

if there is no reason to allow duplicates, then yes, enforce it always
0
 
asp_net2Author Commented:
What type of "expressions" need to be added? What is an expression? Also, I'm confused about using a constraint for Updates since a user would NOT be able to be duplicated if the Constraint is set for Insert right? So if a user needs to update his/her information then I should not have to wory about a constraint when performing an update, right? Please correct me if I'm wrong with an explaination as to why.
0
 
sdstuberCommented:
update your_table  set last_name = 'Everybody'


this will touch every row in the table and give everyone the same last name  if that's bad, then I need a constraint to prevent that.

that's an unlikely scenario; but constraints aren't about what "should" happen,  they are for preventing what "could" happen.


0
 
Scott PletcherSenior DBACommented:
Yes, it is 100% best to let SQL enforce uniqueness.
 

ALTER TABLE dbo.HealthCourses_Users
ADD CONSTRAINT HealthCourses_Users__UQ_flname UNIQUE NONCLUSTERED ( users_flname )

ALTER TABLE dbo.HealthCourses_Users
ADD CONSTRAINT HealthCourses_Users__UQ_email UNIQUE NONCLUSTERED ( users_email )

ALTER TABLE dbo.HealthCourses_Users
ADD CONSTRAINT HealthCourses_Users__UQ_username UNIQUE NONCLUSTERED ( users_username )

SQL will need to create indexes to enforce the rules, but you would need those anyway for a reasonable lookup time.
0
 
asp_net2Author Commented:
@ScottPletcher,

Ok, thanks for the feedback. However, I'm not sure what you mean on your last comment below. If I lookup a user I may look them up by their users_flname or by another field labeld users_id. How will that help me with a reasonable lookup speed?

>> SQL will need to create indexes to enforce the rules, but you would need those anyway for a reasonable lookup time.
0
 
asp_net2Author Commented:
@sdstuber,

Man you raised a VERY GOOD Point, I did not think of that and like you said it may never happen but I would rather program against that then worry about that later in time. I'm very glad you thought of that and mentioned it or I would have been wondering why it was important for a Constraint for Updates.
0
 
Scott PletcherSenior DBACommented:
>> However, I'm not sure what you mean on your last comment below. If I lookup a user I may look them up by their users_flname or by another field labeld users_id. How will that help me with a reasonable lookup speed? <<

Sorry.  What I was trying to say is, even if you tried to enforce the constraints yourself -- for example, by using a trigger -- you would end up creating indexes on all those columns anyway, otherwise your triggers would take too long to run.

Since the indexes are needed anyway, why not just let SQL enforce the rules?  It does it far better than you could with any code.

So, as I stated before, it's *100%* better to let SQL do it.

Btw, you can disable a constraint(s) if for some reason you ever had to violate them.  You could then re-enable them to put the constraint(s) back in place.
0
 
asp_net2Author Commented:
@ScottPletcher,

Ok, so to recap from all this.

- It's always better to let SQL worry about duplicate entry's by using Constraints rather than checking with stored procedures?

Also, can you send me some easy to follow links on tutorials that handle this stuff so I can learn more from it?
0
 
Scott PletcherSenior DBACommented:
Yes, where possible it's always better to let SQL do the checking.  Look also at the CHECK clause itself in table creation.  It can be used to restrict the value(s) in a column.  For example:
CREATE TABLE ...
    col1 tinyint CHECK(col1 BETWEEN 1 AND 5)
    col2 ... col3 ... col4 ...
    --at least one of col2/col3/col4 must have a non-NULL value
    ... CHECK(col2 IS NOT NULL OR col3 IS NOT NULL OR col4 IS NOT NULL)
etc..

Hmm, don't know any links of the top of my head.  Books Online is actually reasonablly good for that kind of stuff.  Or a good book.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.