Avatar of VikingOnline
VikingOnline
Flag for Norway asked on

SQL multiple columns makes row unique

Hey guys,
I have a hopefully simple question, although it became very long to explain it.
I'm looking for the best practice and most efficient solution to do this.

I want to create a SQL table, and in this table two columns makes the row unique when looked at together. And this same entry cannot happen more than once. So each column can have repeats, but together the value of column A + B can only happen once.

I can add a UNIQUE constraint in the SQL table for both columns together. I assume that will make sure that you can have the same value in column A multiple times, and the same value in column B multiple times, but only one A+B.

When I do the VB.NET code to do the insert of new rows into the table, and I know it's a risk that someone has already updated part of this data, should I do a test first to see if the record exists and only pass the insert statement if it does not exist? Or is it better to send the insert statement, and handle the insert error from the SQL server?

I also want to publish a message back to the user like "483 rows out of 500 was updated. 17 rows already existed."

My first thought was:

VB code for:
SELECT statement to see if exist
If not exist, INSERT INTO statement
If exists, add 1 to variable counting number of rows not updated

My second thought was that this must create extra traffic to the SQL server, and I think it's better to let the SQL server test if it can be updated based on the unique constraint, and then push back an error if it cannot

VB code for:
INSERT INTO statement
If error code row already exist, add 1 to variable counting number of rows not updated
Else if other error code, handle error

And what is the best way to handle that on the SQL server side?
I can try to update, and catch if it fails?
Begin try
insert into ....
End try
Begin catch
.........


Or I've seen an example of is a SQL statement that said:
IF EXISTS(SELECT 'True' FROM MyTable WHERE xxxxx = @xxxxx
BEGIN
SELECT 'This record already exists!'
END
ELSE
BEGIN
INSERT INTO MyTable..........
END

I've never used the IF EXISTS SQL statement, but assuming it's a valid option I have 3 possible ways to do this and all 3 would work. This is a process where I might add several hundred or thousand rows into the SQL table, so I'm looking at what would be the most efficient and best way of doing this?
Visual Basic.NETMicrosoft SQL Server

Avatar of undefined
Last Comment
Carl Tawn

8/22/2022 - Mon
ankurdk

Hi,
You can create a constraint as follows:


CREATE TABLE [dbo].[Table1] ADD  CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
      
      [Column1] ASC,
      [Column1] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO


and I suppose this shall take care of all your issues.
Carl Tawn

If you were using a stored procedure rather than inline SQL code, then I would perform the check first before attempting the insert - it's cleaner to check for a possible issue if you can rather than relying on it failing.

If you're in a stored procedure then the overhead of checking first is negligible. If, on the other hand, you are running two separate statements from your VB app, then that will or course create additional network bandwith and delay the response to your app.
VikingOnline

ASKER
Hi Carl, so what you are saying is create a stored procedure and in the SQL query in the stored procedure, test if it exists before doing an insert?

So don't create two calls to the SQL server from the VB code, with one Select statement and one insert statement? But with a stored procedure, I can do both in one call to the SQL server?

That is done by a SQL query in the lines of:

IF EXISTS(SELECT 'True' FROM MyTable WHERE xxxxx = @xxxxx
BEGIN
SELECT 'This record already exists!'
END
ELSE
BEGIN
INSERT INTO MyTable..........
SELECT 'Record inserted!'
END

What would be the VB.NET code be?
Create a normal data reader to read the return of the select statements? Pass the values for the insert as parameters, but other than that treat it as a sql data reader?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Carl Tawn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question