?
Solved

SQL Insert Prevent Duplicates

Posted on 2011-10-24
27
Medium Priority
?
208 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:asp_net2
  • 11
  • 10
  • 3
  • +2
27 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 37018161
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
 
LVL 2

Expert Comment

by:adamnl
ID: 37018162
You can use the SQL keyword 'UNIQUE' for this.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018195
constraints should be declarative rather than procedural


ALTER TABLE dbo.HealthCourses_Users ADD CONSTRAINT uc_HealthCourses UNIQUE (users_flname, users_email , users_username)
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 4

Author Comment

by:asp_net2
ID: 37018196
@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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 37018222
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
 
LVL 4

Author Comment

by:asp_net2
ID: 37018231
@sdstuber,

Can I have both methods? Also, how do I create a CONSTRAINT like you are suggesting?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018244
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
 
LVL 4

Author Comment

by:asp_net2
ID: 37018271
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018301
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
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 37018307
either way should work for you.

It is up to you which one to use.

That's my opinion anyway.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018328
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
 
LVL 4

Author Comment

by:asp_net2
ID: 37018332
@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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018372
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
 
LVL 4

Author Comment

by:asp_net2
ID: 37018588
@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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018612
you start the transaction like you did originally,  the try/catch  replaces your select/if/else
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018618
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
 
LVL 4

Author Comment

by:asp_net2
ID: 37018679
@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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018740
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018750
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
 
LVL 4

Author Comment

by:asp_net2
ID: 37018801
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37018856
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 37020002
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
 
LVL 4

Author Comment

by:asp_net2
ID: 37022077
@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
 
LVL 4

Author Comment

by:asp_net2
ID: 37022084
@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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37025125
>> 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
 
LVL 4

Author Comment

by:asp_net2
ID: 37030405
@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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37030820
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question