How to find which column is violating Constraints?

Question

I have a strongly typed data set which throws this error for null values,

System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. at System.Data.DataTable.EnableConstraints() at System.Data.DataTable.EndLoadData() at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at Fruits.DataAccess.FruitsTableAdapters.FruitsExtTableAdapter.GetFruits(String User, String Filter) at Fruits.DataAccess.FruitsDataAccess.GetFruits(String User, String Filter) at Fruits.WebServices.External.Fruity.GetFruits(String Filter)

All of the columns are populated with 1 single row I am testing it with,

USE [FruitDataBase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Fruits](
    [ID] [int] NOT NULL,
    [CategoryID] [int] NOT NULL,
    [Title] [nvarchar](255) NOT NULL,
    [URL] [nvarchar](255) NOT NULL,
    [Status] [nvarchar](70) NOT NULL,
    [Description] [nvarchar](1024) NULL,
    [User1] [nvarchar](50) NOT NULL,
    [Date] [datetime] NOT NULL,
    [User2] [nvarchar](50) NULL,
    [Date2] [datetime] NULL,
    [Impact] [nvarchar](255) NULL,
    [Solution] [nvarchar](1024) NULL,
 CONSTRAINT [PK_Fruits] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Fruits]  WITH CHECK ADD  CONSTRAINT [FK_Fruits_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Categories] ([ID])
GO

ALTER TABLE [dbo].[Fruits] CHECK CONSTRAINT [FK_Fruits_Categories]
GO

Answer 1

Just in case of somebody is (like me currently) working with the legacy project in VB.NET with the DataSets to save you guys a minute I'm posting a VB.NET version off the @AFract answer. Hope it fits here:

        Catch ex As ConstraintException
            Dim rowErrors = this.YourDataSet.YourDataTable.GetErrors()
            System.Diagnostics.Debug.WriteLine("YourDataTable Errors:" & rowErrors.Length)
            For i = 0 To rowErrors.Length - 1
                For Each col As DataColumn In rowErrors(i).GetColumnsInError()
                    System.Diagnostics.Debug.WriteLine(col.ColumnName & ":" & rowErrors(i).GetColumnError(col))
                Next
            Next
        End Try

Answer 2

I had the same error, although in my case it happened generating a report that gets the information from a stored procedure to the dataset.

In order to solve the problem I inserted the query to a temporary table inside the stored procedure and the final query of the procedure is to that temporary table.

The error is due to the fact that one or more keys (either primary or foreign) are involved in the query and for some reason it generates the exception, I hope to find that exact reason in the .NET documentation, but that was the way I could get that information without problems.

CREATE PROCEDURE [dbo].[RepListadoFacturasProfitAcuerdo] 
-- Add the parameters for the stored procedure here
@id_ciclo int = null,
@id_productor int = null,
@id_acuerdo int = null

AS BEGIN

SET NOCOUNT ON;

if @id_ciclo = 0
    set @id_ciclo = null

if @id_productor = 0
    set @id_productor = null

if @id_acuerdo = 0
    set @id_acuerdo = null

declare @cod_prod varchar(MAX), @co_ciclo varchar(MAX), @num_acuerdo varchar(MAX)

set @cod_prod = (select cod_prod from Productor where identidad=@id_productor)
set @co_ciclo = (select co_ciclo from Ciclos where identidad=@id_ciclo)
set @num_acuerdo = (select num_acuerdo from AcuerdoPrincipal where identidad=@id_acuerdo)

declare @temp table 
(
    [fact_num] [int],
    [fec_emis] [smalldatetime],
    [art_des] [varchar](MAX),
    [neto] [decimal](18,2),
    [numAcuerdo] [varchar](MAX),
    [des_ciclo] [varchar](MAX),
    [des_rubro] [varchar](MAX),
    [des_prod] [varchar](MAX)
)


INSERT INTO @temp ([fact_num],
    [fec_emis],
    [art_des],
    [neto],
    [numAcuerdo],
    [des_ciclo],
    [des_rubro],
    [des_prod] )    

SELECT FV.fact_num, FV.fec_emis, ltrim(rtrim(A.art_des)) as art_des, CONVERT(decimal(18,2),sum(FVR.reng_neto/FV.tasa)) neto,
    FV.campo2 AS numAcuerdo, CIC.des_ciclo, RUB.des_rubro, CLI.cli_des des_prod
         
    from FUT_A.dbo.factura FV
    inner join FUT_A.dbo.reng_fac FVR on FVR.fact_num = FV.fact_num
    inner join FUT_A.dbo.art A on A.co_art = FVR.co_art
    LEFT JOIN Ciclos CIC on CIC.co_ciclo=FV.campo4 COLLATE DATABASE_DEFAULT
    LEFT JOIN Rubros RUB on RUB.identidad = CIC.id_rubro
    LEFT JOIN FUT_A.dbo.clientes CLI on CLI.co_cli=FV.co_cli

WHERE (@co_ciclo is null OR FV.campo4=@co_ciclo)
    AND (@cod_prod is null or FV.co_cli = @cod_prod)
    AND (@num_acuerdo is null or FV.campo2 = @num_acuerdo)
    AND FV.campo2 is not null AND FV.campo4 is not null
    AND des_ciclo is not null AND des_rubro is not null
    AND FV.campo2!=''

GROUP BY A.art_des, FV.fact_num, FV.campo2, FV.fec_emis, CIC.des_ciclo, RUB.des_rubro, CLI.cli_des

SELECT * from @temp

ORDER BY numAcuerdo ASC, fec_emis ASC

END

Answer 3

You can use this method in your code. .NET checks the dataset thus to throw the exception.

       public void CheckDataSet(DataSet dataSet)
       {                                                              
        Assembly assembly = Assembly.LoadFrom(@"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Data.dll");
        Type type = assembly.GetType("System.Data.ConstraintEnumerator");
        ConstructorInfo ctor = type.GetConstructor(new[] { typeof(DataSet) });
        object instance = ctor.Invoke(new object[] { dataSet });                
        BindingFlags bf = BindingFlags.Instance | BindingFlags.Public;
        MethodInfo m_GetNext = type.GetMethod("GetNext", bf);

        while ((bool)m_GetNext.Invoke(instance, null))
        {
            bool flag = false;
            MethodInfo m_GetConstraint = type.GetMethod("GetConstraint", bf);                    
            Constraint constraint = (Constraint) m_GetConstraint.Invoke(instance, null);
            Type constraintType = constraint.GetType();
            BindingFlags bfInternal = BindingFlags.Instance | BindingFlags.NonPublic;
            MethodInfo m_IsConstraintViolated = constraintType.GetMethod("IsConstraintViolated", bfInternal);                    
            flag = (bool)m_IsConstraintViolated.Invoke(constraint, null);
            if (flag)                    
                Debug.WriteLine("Constraint violated, ConstraintName: " + constraint.ConstraintName + ", tableName: " + constraint.Table);                                            
        }

        foreach (DataTable table in dataSet.Tables)
        {
            foreach (DataColumn column in table.Columns)
            {
                Type columnType = column.GetType();
                BindingFlags bfInternal = BindingFlags.Instance | BindingFlags.NonPublic;

                bool flag = false;
                if (!column.AllowDBNull)
                {                            
                    MethodInfo m_IsNotAllowDBNullViolated = columnType.GetMethod("IsNotAllowDBNullViolated", bfInternal);                                                        
                    flag = (bool)m_IsNotAllowDBNullViolated.Invoke(column, null);
                    if (flag)
                    {
                        Debug.WriteLine("DBnull violated  --> ColumnName: " + column.ColumnName + ", tableName: " + column.Table.TableName);
                    }
                }
                if (column.MaxLength >= 0)
                {
                    MethodInfo m_IsMaxLengthViolated = columnType.GetMethod("IsMaxLengthViolated", bfInternal);                            
                    flag = (bool)m_IsMaxLengthViolated.Invoke(column, null);                            
                    if (flag)                            
                        Debug.WriteLine("MaxLength violated --> ColumnName: " + column.ColumnName + ", tableName: " + column.Table.TableName);
                }
            }
        }                                                    
}

Answer 4

DataSet / DataTable have properties to get more details on errors.

So these details are not in the exception itself as you could think, that's the trick. See http://www.codeproject.com/Tips/405938/Debugging-DataSet-Constraint-Errors

Sample :

catch (ConstraintException)
{
    DataRow[] rowErrors = this.YourDataSet.YourDataTable.GetErrors();

    System.Diagnostics.Debug.WriteLine("YourDataTable Errors:" 
        + rowErrors.Length);

    for (int i = 0; i < rowErrors.Length; i++)
    {
        System.Diagnostics.Debug.WriteLine(rowErrors[i].RowError);

        foreach (DataColumn col in rowErrors[i].GetColumnsInError())
        {
            System.Diagnostics.Debug.WriteLine(col.ColumnName 
                + ":" + rowErrors[i].GetColumnError(col));
        }
    }
}

Answer 5

Problem was with one of the column name not matching in the dataset, not sure what triggered it, but matching name of the columns with one returning fixed the issue.

I also used "Preview Data" feature of strongly typed dataset which helped me figure out the problem.