none
How to add a unique constraint to a data table while ignoring blank rows. RRS feed

  • Question

  • Hi,

    I have a data table and I was able to add unique constraint to a data column using 

    dataTable.Constraints.Add(new UniqueConstraint(new DataColumn[] { dataTable.Columns["Name"] }));

    The issue I am trying to solve is, when user decides to insert a bunch of rows first and then enter the data, with this constraint in place, data table throws an unique constraint error. Is there any way to make unique constraint ignore when its blank?

    Monday, February 26, 2018 12:32 PM

Answers

  • Hello Suresh700,

    As far as I try, DataTable object doesn't allow null or empty row for unique constraint. Only thing I found is that MySQL tool allows empty values for unique constraint column.

    https://stackoverflow.com/questions/1346765/unique-constraint-that-allows-empty-values-in-mysql

    If you want to not only add unique constraint but also allow multi empty rows. I suggest you abandon using unique constraint and create a custom check method when add new rows. Just retrieve row data and loop to compare each rows. I think it equals the job with unique constraint in .Net.

    Hope this would be helpful.

    Best regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Fei HuModerator Tuesday, March 6, 2018 2:29 AM
    • Marked as answer by Suresh700 Thursday, March 8, 2018 2:23 PM
    Thursday, March 1, 2018 6:16 AM
    Moderator

All replies

  • Why allow your application to enter more than one row? You should only allow one row at a time and use assertion to ensure the constraint is not violated.

    If the data is to be save to a database table you could place the constraint on the column in the table in the database. Insert one row at a time with a try-catch wrapping the add row and check for a constraint violation as shown here (same works for OleDb data provider with the matching exception).

    Otherwise you would need to remove the constraint then add the constraint back after there are no blank rows and watch out for constraint violations popping up at this point in time. Note if this was a DataSet we can use

    dataSet1.EnforceConstraints = false;
    // Perform some operations on the dataset
    dataSet1.EnforceConstraints = true;
    But still need to be aware of constraint issues when turning them back on.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 26, 2018 1:20 PM
    Moderator
  • This doesn't really make a lot of sense.

    Either you have a constraint that enforces uniqueness or you don't.

    If you allow users to enter a whole bunch of empty rows then that legitimately violates the constraint.

    What would happen if the user did this and then didn't fill in the data afterwards? You would be leaving your database in an inconsistent state; and the entire point of a constraint is to prevent this situation.

    So either have a constraint and force your users to entire data at point of entry. Or don't have a constraint and allow your users to enter a batch of blank rows (which may require some kind of data validation later).

    Monday, February 26, 2018 1:27 PM
  • Hi,

    Thanks for the response. 

    This is the scenario that I am working on, I need to create excel table's with data being sent from server. I am using data table as data source for ListObject (excel table). Also the data will be populated to the data table based on the response from the server. 

    Use case requested by the user is, he wants to first insert a bunch of rows in excel table and then he wants to enter data into it. I already have a check which does the validation for a blank rows, so multiple blank rows should be fine as this gets ignored in the client side. I just wanted to know if there is anything that can be done using the data table constraints to allow this requirement. 

    Tuesday, February 27, 2018 6:10 AM
  • Hello Suresh700,

    As far as I try, DataTable object doesn't allow null or empty row for unique constraint. Only thing I found is that MySQL tool allows empty values for unique constraint column.

    https://stackoverflow.com/questions/1346765/unique-constraint-that-allows-empty-values-in-mysql

    If you want to not only add unique constraint but also allow multi empty rows. I suggest you abandon using unique constraint and create a custom check method when add new rows. Just retrieve row data and loop to compare each rows. I think it equals the job with unique constraint in .Net.

    Hope this would be helpful.

    Best regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Fei HuModerator Tuesday, March 6, 2018 2:29 AM
    • Marked as answer by Suresh700 Thursday, March 8, 2018 2:23 PM
    Thursday, March 1, 2018 6:16 AM
    Moderator
  • This doesn't really make a lot of sense.

    Either you have a constraint that enforces uniqueness or you don't.

    If you allow users to enter a whole bunch of empty rows then that legitimately violates the constraint.

    What would happen if the user did this and then didn't fill in the data afterwards? You would be leaving your database in an inconsistent state; and the entire point of a constraint is to prevent this situation.

    So either have a constraint and force your users to entire data at point of entry. Or don't have a constraint and allow your users to enter a batch of blank rows (which may require some kind of data validation later).

    It makes sense in fringe cases though and I've had to deal with them in a enterprise environment with zero choices.

    On a side note I have a TechNet article on working with constraints.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, August 21, 2018 12:49 PM
    Moderator