Allow only 1 combination for each value.

• hemul

  Right there with Babe

  Points: 734

  More actions

  November 17, 2009 at 3:38 pm

  #214105

  Please help me enforce the following data integrity:

  Each unique value of Col1 must not be combined with more than 1 unique value of Col2.

  CREATE TABLE Test (

  Col1 int NOT NULL,

  Col2 int NOT NULL

  )

  INSERT INTO Test VALUES(1, 2) --Ok.

  INSERT INTO Test VALUES(1, 2) --Ok.

  INSERT INTO Test VALUES(1, 3) --FAIL!

  INSERT INTO Test VALUES(2, 3) --Ok.

  INSERT INTO Test VALUES(2, 3) --Ok.

  INSERT INTO Test VALUES(2, 4) --FAIL!

  Thanks!

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  November 17, 2009 at 3:40 pm

  #1080595

  Just curious, what is the business reason behind this? Why would you want to do this?

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 17, 2009 at 5:29 pm

  #1080630

  If you'd normalize your table structure it would become easy:

  Take your test table, add a primary key and unique constraints on col1 and col2.

  This will give you unique combination.

  To reference those in your target table, use the new primary key.

  Your valid combination would be (based on your sample):

  (1,2) as key 1 and (2,3) as key 2. You wouldn't be able to add (1,3) or (2,4) nor for example (5,2) since all those would violate your constraints.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  November 17, 2009 at 5:55 pm

  #1080633

  Are you trying to create a scenario where every combination must be inserted into the database in pairs? And if the combination is not paired then it should not be inserted?

  The data in your sample portrays this kind of setup, but that conflicts with your written description.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events