Denormalized many-to-many mapping

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开发者_如何学Python?

My requirements are:

• In the whole table there MUST be no rows with the same values for Left1 and Left2 (solved)
• In the whole table there MUST be no rows with the same values for Left1, Left2, Right1, Right2, Right3, Right4, Right5, Right6 (I need help here)

It's completely unnecessary to add a second constraint.

Your unique constraint on

Left1, Left2

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.

(Added)

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.