Consider that, I have the following values in a table.

Now, the following set of insert should work

ABC, 123, L2
ABC, 456, L3
ABC, 789, L1
MNO, 567, L5

But, following set should not insert.

ABC, 123, L1
XYZ, 456, L2

The concatenated string of columns of each row should be unique.

Can someone please help to build the SQL query?

CodePudding user response：

Since you confirmed that you want the concatenation to be unique, a trivial unique constraint like this will not work:

alter table MyTable
add constraint MyUniqueConstraintName 
unique ([Name], [Contact Number], [Location]);

Because that would allow the following insert, contrary to your requirement:

insert T ([Name], [Contact Number], [Location])
values ('abc', 'de', 'f'),
       ('a', 'bc', 'def');

What we can do is add a computed column, and then force the computed column to be unique:

alter table T add 
   Concatenated as concat([Name], [Contact Number], [Location]),
   constraint ConcatenatedValueMustBeUnique unique (Concatenated);

However, one additional wrinkle is that you said that any of the columns can be null. This raises two awkward questions

1. Does null equal null, for the purposes of your requirement?
2. Does "" equal null for the purposes of your requirement?

Here's an example of each:

-- should this be allowed or disallowed?
insert T ([Name], [Contact Number], [Location])
values ('ab', null, 'cd'),
       ('ab', '', 'cd');

-- what about this? 
insert T ([Name], [Contact Number], [Location])
values ('ab', null, 'cd'),
       ('ab', null, 'cd');

In the strictest case (that is to say, the case of the most restrictive constraint), we would say "null equals null, and null also equals the empty string", which you could express as

alter table T add 
   Concatenated as concat
   (
      isnull([Name], ''),
      isnull([Contact Number], ''),
      isnull([Location], '')
   ),
   constraint UniqueConcat unique (Concatenated);

But that is actually equivalent to the original constraint I provided, because concat "eliminates" null (turns it into an empty string), so it's the same as Concatenated as concat([Name], [Contact Number], [Location])

If you require the condition "null equals null but null does not equal the empty string", you might replace the empty string in the isnull expressions above with something like char(0).

But if you want to say "null does not equal null", things actually get rather tricky. So hopefully you don't want to say that!

If you want to do it as code rather than a constraint - I wouldn't recommend it, because what if someone writes data into the table using different code?

CodePudding user response：

You can use the EXCEPT operator:

INSERT INTO dbo.MyTable (Name,[Contact Number],[Location])
SELECT Name,[Contact Number],[Location]
FROM (  VALUES  ('ABC','123','L1') 
        ) as xyz(Name,[Contact Number],[Location])
EXCEPT 
SELECT Name,[Contact Number],[Location]
FROM dbo.MyTable;