In SQL Server 2008 R2 I have a table with the following structure
Id INT NOT NULL PRIMARY KEY,
Left1 INT NOT NULL,
Left2 INT NULL,
Right1 NVARCHAR(128) NOT NULL,
Right2 NVARCHAR(128) NOT NULL,
Right3 NVARCHAR(128) NULL,
Right4 NVARCHAR(128) NULL,
Right5 NVARCHAR(128) NULL,
Right6 NVARCHAR(128) NULL
Which is used to map Left1 and Left2, coming from a system ("Left") into Right1-6, going to another system ("Right").
I need to ensure that the Left columns are unique, which I have done with a unique index, and that the Left-Right combinations are also unique.
I cannot use an index here because the maximum column size supported is 900 bytes. I need to avoid splitting the table in Left, Right and Left2Right - as it's way too tedious and error prone with my use cases.
What is the correct way of enforcing the uniqueness of the combination?
My requirements are:
It's completely unnecessary to add a second constraint.
Your unique constraint on
will prevent duplicate values of
Left1, Left2, Right1, Right2, Right3, Right4, Right5, Right6.
There is no way to duplicate all 8 of those fields without first duplicating the first 2.
One idea: create a lookup table of all possible "Right" values, assign them integer surrogate keys, use those surrogate keys in your Left2Right table, and build your unique constraint on them. Denormalized and ugly as sin, but it could do the trick.
You could also try building a hash of the values and constraining on that. The poor mans’ version would be to add a calcluated row using checksum:
ALTER TABLE MyTable Add RightHash AS checksum(Left1, Left2, Right1, Right2, Right3, Right4, Right5, Right6)
and add a unique constraint on that (it may have to be a persisted calculated column to do this). The main problem with this is that different sets of initial values could produce the same checksum values. You could use one of the (much more) robust hashing algorithms via the hashbytes function, which would have the same limitation (possible duplicate hash values) but the chance of collision would be significantly smaller.