create a constraint over 30 columns to have a Unique combination

• Nicolas BrainPowered.Net

  SSC Journeyman

  Points: 76

  More actions

  March 2, 2012 at 5:34 am

  #252522

  hello

  i have a table with over 60 columns, and i have to implement a constraint on this data set whereby:

  - i can only have single row with the same combination of values on a set of 32 columns which do not allow NULL values (uniqueness)

  since MSSQL only allows unique indexes is limited to 25 columns

  i have considered using a trigger to implement this requirement and add a column to store a calculated HASH for these columns.

  by using a instead-of insert trigger i could fire upon insert and updates and compute a HASH of the values of these columns, check if it is already used in the HASH column and proceed (either insert or rollback).

  is this the best option?

  what would be the alternatives to the above solution to this requirement?

  thanks,

  Nicolas

• Lowell

  SSC Guru

  Points: 323755

  More actions

  March 2, 2012 at 5:42 am

  #1454106

  how about a calculated , persisted column using either a hash or binary checksum, and a unique constraint on that? that gets rid of triggers and is self maintaining.

  ALTER TABLE CMENTITY

  ADD UQCHECKSUM AS BINARY_CHECKSUM(

  IDNUMBER,

  ENTITYNAME,

  ADDRESS,

  ADDRESS2,

  STATETBLKEY,

  COUNTYTBLKEY,

  CITYTBLKEY,

  ZIPCODE) PERSISTED

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  March 2, 2012 at 7:07 am

  #1454146

  An indexed view could also do the trick.

  See here: http://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/

  -- Gianluca Sartori

• Nicolas BrainPowered.Net

  SSC Journeyman

  Points: 76

  More actions

  March 7, 2012 at 3:57 am

  #1455854

  i really like the idea you proposed

  but the binary_checksum() over my data returns a high percentage of colision (different values in columns give same results)

  so way i managed to avoid collisions was by adding a concept of 'position'

  and use the hashbytes() with SHA1

  sort of:

  HashBytes('SHA1',

  'p01' +col01

  + 'p02' +col02

  + 'p03' +col03

  + 'p04' +col04

  + 'p05' +col05

  + 'p06' +col06

  + 'p07' +col07

  + 'p08' +col08

  + 'p09' +col09

  + 'p10' +col10

  + 'p11' +col11

  + 'p12' +col12

  + 'p13' +col13

  + 'p14' +col14

  + 'p15' +col15

  + 'p16' +col16

  + 'p17' +col17

  + 'p18' +col18

  + 'p19' +col19

  + 'p20' +col20

  + 'p21' +col21

  + 'p22' +col22

  + 'p23' +col23

  + 'p24' +col24

  + 'p25' +col25

  + 'p26' +col26

  + 'p27' +col27

  + 'p28' +col28

  + 'p29' +col29

  + 'p30' +col30

  )

  i find this solution very loose (if not wrong) *but* it delivers the goods.

  after implementing the above checksum() i have successfully added a unique index over this column.

• Jeff Moden

  SSC Guru

  Points: 1004706

  More actions

  March 7, 2012 at 7:36 am

  #1456002

  Nicolas BrainPowered.Net (3/7/2012)

  ---

  i find this solution very loose (if not wrong) *but* it delivers the goods.

  after implementing the above checksum() i have successfully added a unique index over this column.

  I'm curious. What do you find to be "very loose (if not wrong)" with the solution? Why is a list of columns to include in a hash any worse than than a 32 column constraint?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Nicolas BrainPowered.Net

  SSC Journeyman

  Points: 76

  More actions

  March 9, 2012 at 7:35 am

  #1457201

  the trouble that i see is in having 2 row like:

  col1 col2 col3

  A NULL NULL

  NULL NULL A

  Note: <N> means NULL

  since the hashbyte() or checksum() hashes a string

  both rows would generate a string such as:

  'A'

  and have the same value for their hashes.

  so the only way that i could think was to added the position (p01,p02,..)

  to the string, to ensure that this problem would not occur.