none
How To Create A Unique Constraint On Multiple Columns RRS feed

  • Question

  • Hello:

    I am using a VB Interface With MS Acess Database. I have figured out how to create a unique constraint (to prevent

    duplicate entries) on one column as follows:

     Dim Unique As New UniqueConstraint(dsPSB.Tables("dtSE").Columns("SE_NAME"))
     dsPSB.Tables("dtSE").Constraints.Add(Unique)

    I tried to add another column to the constraint but the syntax must be incorrect.

     Dim Unique As New UniqueConstraint(dsPSB.Tables("dtSE").Columns("SE_NAME"),   dsPSB.Tables("dtSE").Columns("SE_CTRY"))

     dsPSB.Tables("dtSE").Constraints.Add(Unique)

    I would really appreciate some help.

    Thanks. John.

    Monday, February 4, 2019 6:37 PM

Answers

  • Hi

    Give this a try (I don't have your database to ttest with)

     Dim Const1 As New UniqueConstraint(dsPSB.Tables("dtSE").Columns("SE_NAME"))
     Dim Const2 As New UniqueConstraint(dsPSB.Tables("dtSE").Columns("SE_CTRY"))
    
     dsPSB.Tables("dtSE").Constraints.AddRange({Const1, Const2})
    


    Regards Les, Livingston, Scotland

    • Marked as answer by Developer Dude Tuesday, February 12, 2019 6:00 PM
    Monday, February 4, 2019 7:05 PM
  • Try this fix:

    Dim Unique As New UniqueConstraint({dsPSB.Tables("dtSE").Columns("SE_NAME"), dsPSB.Tables("dtSE").Columns("SE_CTRY")})
    

    Monday, February 4, 2019 7:15 PM

All replies

  • Hi

    Give this a try (I don't have your database to ttest with)

     Dim Const1 As New UniqueConstraint(dsPSB.Tables("dtSE").Columns("SE_NAME"))
     Dim Const2 As New UniqueConstraint(dsPSB.Tables("dtSE").Columns("SE_CTRY"))
    
     dsPSB.Tables("dtSE").Constraints.AddRange({Const1, Const2})
    


    Regards Les, Livingston, Scotland

    • Marked as answer by Developer Dude Tuesday, February 12, 2019 6:00 PM
    Monday, February 4, 2019 7:05 PM
  • Try this fix:

    Dim Unique As New UniqueConstraint({dsPSB.Tables("dtSE").Columns("SE_NAME"), dsPSB.Tables("dtSE").Columns("SE_CTRY")})
    

    Monday, February 4, 2019 7:15 PM
  • Hi Les:

    Thanks. I need to take it one step further so that both constraints have to be "broken"

    before an error message is displayed.

    John


    Monday, February 4, 2019 7:16 PM
  • That is perfect Les. Thanks a lot!

    Monday, February 4, 2019 7:29 PM
  • That is perfect Les. Thanks a lot!

    Hi

    Oh OK then, but is it not Viorel that you meant?

    Here was my alternative that sets it so that BOTH constraints need to be 'broken' before exception.

          dsPSB.Tables("dtSE").Constraints.Add(New UniqueConstraint(New DataColumn() {dsPSB.Tables("dtSE").Columns("SE_NAME"), dsPSB.Tables("dtSE").Columns("SE_CTRY")}))


    Regards Les, Livingston, Scotland


    • Edited by leshay Monday, February 4, 2019 7:38 PM
    Monday, February 4, 2019 7:36 PM