1

I have a table where I would like to only have unique combinations of strings:

colA     colB
----     ----
bob      tom
ann      bob
tom      tom

How do I constrain this table so that (tom, bob) cannot be inserted? This would be a non-unique combination because of the existence of (bob, tom) in the first record.

A similar question has been asked here, but I believe that question focuses on handling this when the data are integers.

user276833
  • 77
  • 6
  • Add unique constraint on multiple columns will do that. Use below query to alter table and constraint to it : ALTER TABLE dbo.TableName ADD CONSTRAINT Constraint_Name UNIQUE (colA, colB) – Ankur Bhutani Oct 02 '18 at 18:24

2 Answers2

1

I prefer to have a unique index vs a unique constraint. I've found that on columns that require combined uniqueness I probably need an index anyways. 2 birds with 1 stone.

So for this table:

CREATE TABLE [dbo].[mytable](
    [colA] [varchar](50) NULL,
    [colB] [varchar](50) NULL
) ON [PRIMARY]
GO

You could have:

CREATE UNIQUE NONCLUSTERED INDEX [UIDX] ON [dbo].[mytable]
(
    [colA] ASC,
    [colB] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

This will prevent an insert of (tom, bob) twice. The given example has tom twice in the right column already so I'm assuming row 3 is legal thus my answer, so if I'm understanding the question this should work.

sniperd
  • 5,124
  • 6
  • 28
  • 44
  • The existence of the first row, `(bob, tom)`, should prevent the insertion of `(tom, bob)` since this is a non-unique combination. I've updated my question to clarify this. – user276833 Oct 02 '18 at 18:40
0

You could do this with either a TRIGGER or a CHECK CONSTRAINT.

Either way you would check the logic and make sure no row already exists where

(NewA = OldA AND NewB=OldB)
OR
(NewA = OldB AND NewB=OldA)
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52