Best method of checking for duplicate entries in SQL Server

Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional.

In my Favorites table, the 3 fields are....

favoriteId (set as primary key)
userId
carId

I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives.

1) Check proactively by doing a....
SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)
If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table.

2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique.

Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice?

TIA for your help.

Your best bet IMO would be a derivative of option #1.

If NOT Exists(select favoriteID from favorites where userid = @.x and carid = @.y)
BEGIN
--insert record here
return 0
END
ELSE
BEGIN
return 1
END

where a return of 1 means the record exists

|||

Thanks for the reply, Diamsorn. Is that something called a stored procedure? I'm not terribly familiar with them.... but if that's the only way to go, I'll try to research them. Is there a way to translate that into an inline sql query in my VB code? I've been using the SqlCommand object with an SqlDataReader to get at my queries. Is your method noticeably more efficient that mine? Here's my snippet of code where I proactively check for an existing entry, then I do one of two actions depending on whether or not the record exists. Thanks for helping out an ignorant.

Dim favoriteIdAsInteger
Dim favoriteLookupCmdAsNew SqlCommand _
("SELECT favoriteId FROM Favorites WHERE userId = " & userId &" AND carId = " & carId, dbConnection)
thisReader = favoriteLookupCmd.ExecuteReader()
While (thisReader.Read())
favoriteId = thisReader.GetValue(0).ToString
EndWhile
thisReader.Close()

If (String.IsNullOrEmpty(favoriteId) =False)Then
addFavStatusLabel.Text ="This car already exists in your favorites list."
Else
Dim rowsAffectedAsInteger
Dim insertFavoriteCmdAs SqlCommand =New SqlCommand("INSERT INTO Favorites (userId, carId) VALUES (" & userId &", " & carId &")", dbConnection)
rowsAffected = insertFavoriteCmd.ExecuteNonQuery()
If rowsAffected <> 1Then
addFavStatusLabel.Text ="Error in adding car."
Else
addFavStatusLabel.Text ="Car added to favorites successfully."
EndIf
EndIf

Thanks.

|||

Its not a stored procedure, but rather the T-SQL that will do the same as what you are doing above inside of a stored procedure.

Either method is fine, doing it in a single stored procedure reduces the number of trips back to the server your app has to make,

Also you reduce the chance for SQL injection attacks.
If your going to go with the method you have above, then your going to want to use parameters to also reduce the chance for SQL injection attacks.

 
Dim favoriteLookupCmdAs New SqlCommand _ ("SELECT favoriteId FROM Favorites WHERE userId = @.useriD AND carId = @.carID", dbConnection)favoriteLookupCmd.Parameters.AddWithValue("@.useriD", userID)favoriteLookupCmd.Parameters.AddWithValue("@.carID", carID)
 

and do the same for your 2nd sql statement as well.|||

INSERT INTO Favorates(UserID,CarID) SELECT @.UserID,@.CarID WHERE NOT EXISTS(SELECT * FROM Favorates WHEREuserid=@.UserID ANDcarId=@.CarID)

because the check and insert are wrapped into a single SQL Statement, locks are automatically placed on any records it matches in the WHERE clause until the INSERT has completed. This guarantees that you will never insert records into the favorates table that already has the record. You could also do this:

IF NOT EXISTS(...) INSERT ...

but the locks (read/shared) on the records in the exists clause are released prior to the insert, which leaves an opportunity for a record to be inserted between them.

Dim rowsAffectedAsInteger
Dim insertFavoriteCmdAs SqlCommand =New SqlCommand("INSERT INTO Favorites (userId, carId) SELECT @.userid,@.caridWHERE NOT EXISTS(SELECT * FROM Favorates WHEREuserid=@.UserID ANDcarId=@.CarID)", dbConnection)

with insertFavoriteCmd

.Parameters.Add("@.userid",sqldbtype.nvarchar).value=userid

.Parameters.Add("@.carid",sqldbtype.integer).Value=carid

end with
rowsAffected = insertFavoriteCmd.ExecuteNonQuery()
If rowsAffected <> 1Then
addFavStatusLabel.Text ="This car already exists in your favorites list."
Else
addFavStatusLabel.Text ="Car added to favorites successfully."
EndIf

|||

How should i write update query for the same ?

plz help me out of this guys....