DataTable. Foreign key does not works.

  • Thread starter Thread starter c4p
  • Start date Start date
C

c4p

Hello to all.

I have simple form with DataGrid binded to DataTable.
DataTable has foreign key to itself.
When I'm deleting one row it's child rows stay in Table.
(It deletes only parent row, not child).

Here is code:

DataTable TestTable = new DataTable("Test");

TestTable.Columns.Add("ID", typeof(Int32));
TestTable.Columns.Add("ParentID", typeof(Int32));

TestTable.Constraints.Add("PARENT_TO_ID",
TestTable.Columns["ID"],
TestTable.Columns["ParentID"]);

DataRow TestRow = TestTable.NewRow();
TestRow["ID"] = 1;
TestRow["ParentID"] = DBNull.Value;
TestTable.Rows.Add(TestRow);

TestRow = TestTable.NewRow();
TestRow["ID"] = 2;
TestRow["ParentID"] = 1;
TestTable.Rows.Add(TestRow);

dataGrid1.DataSource = TestTable;

Is it DataTable bug?
Thanks in advance!
 
First, I think your syntax needs some help.

TestTable.Constraints.Add(New ForeignKeyConstraint("FK_Parent_ID", _
TestTable.Columns["ID"],
TestTable.Columns["ParentID"])

Second, is [ID] the parent's primary key, and [ParentID]
the child's key that matches the parent? If not, then I think
you need to reverse those.

Third, why are you setting up a DT with a foreign key to itself?

Robin S.
 
Oh, it needs another parentheses, darn it.

TestTable.Constraints.Add(New ForeignKeyConstraint("FK_Parent_ID", _
TestTable.Columns["ID"],
TestTable.Columns["ParentID"]))

Robin S.
-------------
RobinS said:
First, I think your syntax needs some help.

TestTable.Constraints.Add(New ForeignKeyConstraint("FK_Parent_ID", _
TestTable.Columns["ID"],
TestTable.Columns["ParentID"])

Second, is [ID] the parent's primary key, and [ParentID]
the child's key that matches the parent? If not, then I think
you need to reverse those.

Third, why are you setting up a DT with a foreign key to itself?

Robin S.
-------------------------------------
c4p said:
Hello to all.

I have simple form with DataGrid binded to DataTable.
DataTable has foreign key to itself.
When I'm deleting one row it's child rows stay in Table.
(It deletes only parent row, not child).

Here is code:

DataTable TestTable = new DataTable("Test");

TestTable.Columns.Add("ID", typeof(Int32));
TestTable.Columns.Add("ParentID", typeof(Int32));

TestTable.Constraints.Add("PARENT_TO_ID",
TestTable.Columns["ID"],
TestTable.Columns["ParentID"]);

DataRow TestRow = TestTable.NewRow();
TestRow["ID"] = 1;
TestRow["ParentID"] = DBNull.Value;
TestTable.Rows.Add(TestRow);

TestRow = TestTable.NewRow();
TestRow["ID"] = 2;
TestRow["ParentID"] = 1;
TestTable.Rows.Add(TestRow);

dataGrid1.DataSource = TestTable;

Is it DataTable bug?
Thanks in advance!
Click to expand...
Click to expand...
 
Did you mean something like this:

ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Parent_ID",
TestTable.Columns["ID"], TestTable.Columns["ParentID"]);
TestTable.Constraints.Add(fk);

It doesn't work too.

Thanks for reply! :)
 
Is ID the primary key for the parent, and ParentID the
matching entry for the child?

Robin S.
 
Yea!
When I delete Row with ID=1, all rows with ParentID=1 must be deleted
to...
I just cant understand why it's not work...
 
Do you have the DataRelation defined to cascade changes?

Hello

No, I don't have DataRelation. I'm using only DataTable, without
DataSet. Unfortunately I can't use DataSet.
Can I do it wthout DataSet?
 
Do you have the DataRelation defined to cascade changes?

Hello

No, I don't have DataRelation. I'm using only DataTable, without
DataSet. Unfortunately I can't use DataSet.
Can I do it wthout DataSet?
 
Why do you have parents and children in the same table?
What does the table definition look like?

Robin S.
--------------------------------------
 
Hello.
This table is hierarchy.
For example we have such table:

ID ParentID
1 null
2 1
3 1
4 2
5 null

The hierarchy will look like (the number is ID):
|
|_1
| |_2
| | |_4
| |_3
|_4

I've created special control DataTreeView which displays this hierarchy.
 
When you define the foreign key, add values for DeleteRule
and UpdateRule so it will cascade the changes. They should
default to Cascade, but just in case, set them.

Also, try setting EnforceConstraints to true. I'm hoping
this is a property of a DataTable. In the example on MSDN,
they apply this to the DataSet containing the two tables.

ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Parent_ID",
TestTable.Columns["ID"], TestTable.Columns["ParentID"]);
//***Add these two lines.
fk.DeleteRule = Rule.Cascade;
fk.UpdateRule = Rule.Cascade;
TestTable.Constraints.Add(fk);
//Add this line and see if it works.
TestTable.EnforceConstraints = True;

Does that help at all? I don't even know if you can do what
you're trying to do, but maybe this will work.

Robin S.
 
Hello!
I've tryed it before. It doesn't works too.
EnforceConstraints is property of DataSet.
Thanks for advices!
 
I'd say what you're trying to do isn't possible then.
Have you considered showing the data in two grids as
parents and children?

Robin S.
 
I have own UserControl called DataTreeView which displays hierarhical
tables.
It's a pitty that such activity can't be done only with DataTable.

Yesterday I've tryed to do such task with DataSet. It works! But I
can't use DataSet...
 
Back
Top