C# DataTable PrimaryKey Not Working Fix

In this tutorial, you'll learn about C# DataTable PrimaryKey Not Working Fix. We cover key concepts, practical examples, and best practices.

Setting the PrimaryKey on a DataTable but Rows.Find() returns null:

table.PrimaryKey = new[] { table.Columns["ID"] };
var found = table.Rows.Find(42); // null!

The PrimaryKey property sets unique constraints but does not automatically populate the index. Find() requires the column to have unique values and the primary key columns must allow no null values. If any row has null in the key column, the lookup fails silently.

Step-by-Step Fix

1. Ensure key column has no nulls

WRONG — setting PrimaryKey on a column with null values:

DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Rows.Add(null); // DBNull value
table.PrimaryKey = new[] { table.Columns["ID"] };
DataRow found = table.Rows.Find(null); // throws StrongTypingException or returns null

RIGHT — set AllowDBNull to false and handle nulls:

DataTable table = new DataTable();
DataColumn idCol = new DataColumn("ID", typeof(int));
idCol.AllowDBNull = false;
table.Columns.Add(idCol);
table.Rows.Add(1);
table.Rows.Add(2);
table.PrimaryKey = new[] { idCol };
DataRow found = table.Rows.Find(2);
Console.WriteLine(found["ID"]); // 2

2. Set PrimaryKey before adding data for best performance

WRONG — adding data before setting the key:

table.Rows.Add(1, "Alice");
table.Rows.Add(2, "Bob");
table.PrimaryKey = new[] { table.Columns["ID"] }; // re-indexes the table

RIGHT — set PrimaryKey first, then add data:

table.PrimaryKey = new[] { table.Columns["ID"] };
table.Rows.Add(1, "Alice");
table.Rows.Add(2, "Bob");
DataRow found = table.Rows.Find(1);

3. Use composite primary keys correctly

WRONG — passing a single value for a composite key:

table.PrimaryKey = new[] { table.Columns["OrderID"], table.Columns["ProductID"] };
DataRow found = table.Rows.Find(1001); // wrong — needs both keys

RIGHT — pass an object array:

table.PrimaryKey = new[] { table.Columns["OrderID"], table.Columns["ProductID"] };
DataRow found = table.Rows.Find(new object[] { 1001, 5 });
Console.WriteLine(found["Quantity"]);

4. Check for duplicate primary key exceptions

WRONG — adding duplicate keys throws an exception:

table.PrimaryKey = new[] { table.Columns["ID"] };
table.Rows.Add(1, "Alice");
table.Rows.Add(1, "Bob"); // throws ConstraintException

RIGHT — check for existence before adding:

table.PrimaryKey = new[] { table.Columns["ID"] };
if (table.Rows.Find(1) == null)
{
    table.Rows.Add(1, "Alice");
}
else
{
    Console.WriteLine("Row with ID 1 already exists");
}

Expected output: "Row with ID 1 already exists"

Prevention

• Set AllowDBNull = false on primary key columns.
• Set Unique = true on columns that should be unique.
• Set PrimaryKey before populating the DataTable for best performance.
• Validate data for duplicates before calling AcceptChanges().
• Use TryGetValue pattern or check for null after Find().

Common Mistakes with datatable error

1. Misunderstanding that String is [Char] with poor performance for large text operations
2. Using foldl instead of foldl' causing stack overflow on large lists
3. Forgetting deriving (Show, Eq) on custom data types needed for debugging

These mistakes appear frequently in real-world CSHARP code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ