• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

The next problem to this question

We came across the next problem today.  

When existing data in the table contains the reverse condition, we cannot add any other records.

The Table contains the following:
GA = 111, GB = 222
GA = 222, GB = 111
Then we cannot add this:
GA = 222, GB = 333


see previous question
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24984214.html?cid=1575#a26065700
0
tampsystems
Asked:
tampsystems
  • 4
  • 2
1 Solution
 
tampsystemsAuthor Commented:
Also, i tried to add the following record and it did not work.
Table contains:
111, 222
333, 444
555, 444

Record to insert
222, 444

I think maybe the trigger was incorrect?
0
 
tampsystemsAuthor Commented:
And this did not allow me to insert too:
Table contains:
B7, B6
F8, C7

Record to insert
EC, C7
0
 
lofCommented:
Here is my simple solution for the problem. Does not require any triggers.

You have to add additional computed column to the table, which will convert a pair of unique identifiers into 32 byte long binary data which will be exactly the same regardless of the orther

alter table MyTable add BinaryCheckSum
as case when GA > GB then
      CONVERT(binary(16),GB) + convert(binary(16),GA)
else
      CONVERT(binary(16),GA) + convert(binary(16),GB)
end

then you have to create a unique index on the computed column

alter table MyTable add constraint PK_UniquePair unique (BinaryCheckSum)

If you can add extra table that's very simple and effective solution

Regards
Michal
alter table MyTable add BinaryCheckSum 
as case when GA > GB then
	CONVERT(binary(16),GB) + convert(binary(16),GA) 
else
	CONVERT(binary(16),GA) + convert(binary(16),GB) 
end
go

alter table MyTable add constraint PK_UniquePair unique (BinaryCheckSum)

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tampsystemsAuthor Commented:
wow, that was simple and gunius at the same time! thanks!
0
 
tampsystemsAuthor Commented:
This worked great from using the query analyzer but we ran in to trouble when executing an insert from our application.  We got the following error.  Do you have any ideas on what is causing this?  We believe there is something going wrong when the binarychecksum column is being calculated.

"INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods."
0
 
lofCommented:
Is it possible that in your code you are trying to insert null value?

you may try for debuging set this

SET ARITHABORT OFF;
GO

and then see what the BinaryCheckSum looks like. I assumed the key may not be null and in case any of two fields is null the result will be null.

remember to set the ARTIHABORT back to on after testing
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now