CODEWITHSUNDEEP
c#.netdatasetunique-constraint
pgn674
pgn674

Reputation: 95

ADO.NET DataSet - Have a UniqueConstraint for Columns from Different Tables

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 doesn't mention multiple tables, and it looks like no one else has tried this.

I've finished with the problem. 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.

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.

Upvotes: 1

Views: 1341

Answers (1)

shf301
shf301

Reputation: 31394

The MSDN documentation specifically mentions a single DataTable:

The UniqueConstraint object, which can be assigned either to a single column or to an array of columns in a DataTable (emphasis mine)

I don't think you'll be able to get the DataTable to enforce this for you, you'll have to enforce the behavior manually.

Upvotes: 2

Related Questions