none
Have a UniqueConstraint for Columns from Different Tables RRS feed

  • Question

  • I am writing a program in C# in .NET 3.5 in Visual Studio 2008. I have a DataSet with two DataTables, A and B. In table A is DataColumn Y, and in table B is column Z, plus more columns in both tables. Both columns Y and Z are set to be Unique, AutoIncrementing columns.

    I would like Y and Z to also be unique between the two of them. As in, if table A already has a row with a 3 in the Y column, and I'm creating a new row in table B and it wants to put a 3 in the Z column in that new row, it will instead skip 3 and put in a 4.

    I have tried something that looks like it should work:

    myDataSet.Tables["A"].Constraints.Add(new UniqueConstraint(new DataColumn[] { myDataSet.Tables["A"].Columns["Y"], myDataSet.Tables["B"].Columns["Z"] }));
    myDataSet.Tables["B"].Constraints.Add(new UniqueConstraint(new DataColumn[] { myDataSet.Tables["A"].Columns["Y"], myDataSet.Tables["B"].Columns["Z"] }));
    

    But, I get the following error on the first line if I run the application and it gets to that code:

    System.Data.InvalidConstraintException was unhandled
    Message="Cannot create a Key from Columns that belong to different tables."
    Source="System.Data"

    The reason that I add the same UniqueConstraint to both tables is because, well, I'm not really sure where else to put it to make it be active. I would rather not combine the tables into one, as they have little to do with each other, other than columns Y and Z.

    Does anyone know any way to do this? The MSDN documentation says UniqueConstraint is for use "in a DataTable", so I see that multiple tables isn't allowed. But is there any way to make DataSet enforce this for me? It looks like no one else has tried this.
    Sunday, December 12, 2010 6:50 PM

Answers

  • That isn't really quite the same, as it doesn't deal with multiple tables at all, though some ideas are brought forward that could apply in my situation.

    In any case, in my program I had already been using some foreign key checks to keep track of which table I was dealing with. I had thought it might be easier to go about it another way from now on, but I decided to keep my current techniques (expanding it a bit where needed, such as putting more detailed identity information in other components of my program), and it's turned out to work well.

    Thank you for your response.

    What I ended up doing doesn't really solve my question I posted here. I apologize to the Googlers hoping to get an answer here.

    Thursday, December 23, 2010 12:33 AM

All replies

  • Hello pgn674,

    Welcome to MSDN forum,  and thank you for posting in MSDN Forum.

    My name is Jackie Sun and I will be working with you on this case.  So please feel free to let me know if I have misunderstood anything. 

    I think you have the similar problem with this one:

    http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/4e3910eb-8f8f-4d15-a01b-4aaefe334fca/

    So I suggest you can read this topic as reference. 

     

    I hope this will help resolve your problem. If anything is unclear, please free feel to let us know.

     

    Thank you for your understanding and support.

    I look forward to hearing from you.

     

    Best Regards,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 16, 2010 2:45 AM
    Moderator
  • That isn't really quite the same, as it doesn't deal with multiple tables at all, though some ideas are brought forward that could apply in my situation.

    In any case, in my program I had already been using some foreign key checks to keep track of which table I was dealing with. I had thought it might be easier to go about it another way from now on, but I decided to keep my current techniques (expanding it a bit where needed, such as putting more detailed identity information in other components of my program), and it's turned out to work well.

    Thank you for your response.

    What I ended up doing doesn't really solve my question I posted here. I apologize to the Googlers hoping to get an answer here.

    Thursday, December 23, 2010 12:33 AM
  • Hi pgn674,

     

    Thanks for your feedback.

    It’s good to see that it works well and  apologize for helping you little.  If you have no question, I will close this topic.  Thank you again for your understanding and support.

    If you have any question, please feel free to let us know, we will try our best to solve the problem.

     

    Merry Christmas and Happy New Year,

     


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 23, 2010 3:49 AM
    Moderator