DataTable PrimaryKey problem

calhuskerfan

I have a database which has a unique primary key defined key on two columns, in the example below the columns are type and data.

type

value

data

9

0

'ABC'

9

0

'ABC '

When I read in a datatable and then set the PrimaryKey collection with the designated columns I get an exception saying that 'These columns don't currently have unique values'. So, to me it appears that ADO is ignoring the spaces with respect to determining uniqueness (although the whitespace appears in the rows collection). I started looking into the DataTable and DataColumn object to see if there was anything that jumped out, but nothing so far. Can anyone confirm this behavior and/or offer any assistance? Thanks!

led mike

calhuskerfan wrote:

I have a database

calhuskerfan wrote:

So, to me it appears that ADO is

ADO is NOT a database. If you are setting up the keys and constraints in the database it is the database product that will contain your answer not ADO.

led mike

calhuskerfan

The constraints in the database are fine. They allow the described scenario where 'ABC' and 'ABC ' are considered unique. When I read, with ADO, from the database into a datatable object ADO does not bring along the primary key identified in the database. So it allows at this point the two above rows to exist. When I set the PrimaryKey property on the DataTable object (after the rows have been read) to include the column containing the above data I get the constraint exception. Edit - The exception is actually an ArgumentException stating that the values are not unique.

led mike

calhuskerfan wrote:

The constraints in the database are fine. They allow the described scenario where 'ABC' and 'ABC ' are considered unique.

What database are you using? I cannot reproduce that behavior using SQL Express 2005.

led mike

calhuskerfan

Sybase SQL Anywhere 10. Thanks.

calhuskerfan

Here is a very quick (and sloppy) example that reproduces the behavior.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data;

namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
Test1();
}

    public static void Test1()
    {
        DataTable dt1 = new DataTable();
        //
        dt1.Columns.Add("col0");
        dt1.Columns.Add("col1");
        dt1.Columns.Add("col2");
        //
        dt1.Columns\[0\].DataType = typeof(Int32);
        dt1.Columns\[1\].DataType = typeof(String);
        dt1.Columns\[2\].DataType = typeof(String);
        //
        DataRow dr = dt1.NewRow();
        dr\["col0"\] = 1;
        dr\["col1"\] = "Column 2";
        dr\["col2"\] = "Column 3";
        dt1.Rows.Add(dr);
        //
        dr = dt1.NewRow();
        dr\["col0"\] = 1;
        dr\["col1"\] = "Column 2";
        dr\["col2"\] = "Column 3   ";
        dt1.Rows.Add(dr);
        //
        try
        {
            dt1.PrimaryKey = new DataColumn\[\] { dt1.Columns\[0\], dt1.Columns\[2\] };
        }
        catch (ArgumentException ae)
        {
            MessageBox.Show(ae.Message);
        }
    }
}

}

led mike

calhuskerfan wrote:

Here is a very quick (and sloppy) example that reproduces the behavior.

You didn't understand my post. Using SQL Express 2005 with a Table1 designed as your example with the two column primary key constraint. One row in the table as follows:

Type Value Data

1 2 ABC

Executing the following SQL

insert into Table1 (Type, Value, Data) VALUES(1, 3, 'ABC ')

Produces this error: Cannot insert duplicate key row in object 'dbo.Table1' with unique index 'IX_Table1'.

led mike

Wendelius

I tried your test case and ran into the same problem. Seems that when the values are compared, the trailing spaces are removed (or added until the length is the same). Actually the same effect can happen in SQL Server when varchar data type is used so this may be by design. Quickly cannot think anything else than replacing trailing spaces with a special character that can be later removed (like ascii 255).

The need to optimize rises from a bad design. My articles[^]