Primary Keys in Relations

[Blake Weaver]

When I create a datarelation between two of the tables in my dataset, it
creates a key on the appropriate column of the parent table. Am I wrong in
assuming that a PK column cannot have Null Values? And if they can't, does
that mean you must have at least one child record for every parent so the PK
can link to something?

The reason I ask is that I'm getting a constraint violation when I try to
fill the child table via the dataadapter. And I know there is not a child
record for every parent record. In ADO (been a little while but) I think it
would have been an LEFT OUTER JOIN. What's ADO.NET's answer to this? Is this
where the 'Foreign Key Constraint Only' check box comes into play?

Thanks,
Blake

 

[Miha Markic]

Hi Blake,

When I create a datarelation between two of the tables in my dataset, it
creates a key on the appropriate column of the parent table. Am I wrong in
assuming that a PK column cannot have Null Values?

Correct.

And if they can't, does

that mean you must have at least one child record for every parent so the PK
can link to something?

No, PK is the parent key in your situation - it relates to foreign key
(which can be null if desired).
The right side of equation doed never require a row.

You should fill parent table first and then child table.
Or disable constraints before filling and re-enable them after.

 

[Blake Weaver]

Miha, I was assuming it worked exactly the way you described, but it doesn't
seem to be doing so. Let me give you my code:



dsTicketMain.EnforceConstraints = False

dascItemsMain.SelectCommand.CommandText = "SELECT ItemID,
TicketID, BookID, PurchaseOrderID, Description, QtyOrdered, UnitPrice,
QtyReceived, EstFreight, ActFreightPaid, Leadtime, Purchased FROM scItems"

dascItemsMain.Fill(dsTicketMain, "scItems")

dascProblemsMain.SelectCommand.CommandText = "SELECT
ProblemID, ProblemType, ProblemDesc, ItemID FROM scProblems"

dascProblemsMain.Fill(dsTicketMain, "scProblems")

dsTicketMain.EnforceConstraints = True ' This is line 209



When I execute, it throws the following error:




System.Data.ConstraintException: Failed to enable constraints. One or more
rows contain values violating non-null, unique, or foreign-key constraints.

at System.Data.DataSet.FailedEnableConstraints()

at System.Data.DataSet.EnableConstraints()

at System.Data.DataSet.set_EnforceConstraints(Boolean value)

at ServiceCenter.frmDataLoad.GenerateTicketInfo() in
C:\Projects\Code\Uniway\ServiceCenter\frmDataLoad.vb:line 209



And now for the data in my tables:



scItems:

4 rows.

Two of which have ProblemIDs that each correspond to a different row in the
scProblems table.

Two of which have Null ProblemIDs, simply because there were no problems
with those particular items.



scProblems:

2 rows.

Both rows have distinct ProblemIDs



So, the two Null ProblemIDs in the scItems table are obviously causing me
problems. At least thats my assumption. Again, what am I missing?



Thanks,

Blake

 

[Miha Markic [MVP C#]]

Hi Blake,

Inline.

Miha, I was assuming it worked exactly the way you described, but it doesn't
seem to be doing so. Let me give you my code:



dsTicketMain.EnforceConstraints = False

dascItemsMain.SelectCommand.CommandText = "SELECT ItemID,
TicketID, BookID, PurchaseOrderID, Description, QtyOrdered, UnitPrice,
QtyReceived, EstFreight, ActFreightPaid, Leadtime, Purchased FROM scItems"

dascItemsMain.Fill(dsTicketMain, "scItems")

dascProblemsMain.SelectCommand.CommandText = "SELECT
ProblemID, ProblemType, ProblemDesc, ItemID FROM scProblems"

dascProblemsMain.Fill(dsTicketMain, "scProblems")

dsTicketMain.EnforceConstraints = True ' This is line 209



When I execute, it throws the following error:




System.Data.ConstraintException: Failed to enable constraints. One or more
rows contain values violating non-null, unique, or foreign-key constraints.

at System.Data.DataSet.FailedEnableConstraints()

at System.Data.DataSet.EnableConstraints()

at System.Data.DataSet.set_EnforceConstraints(Boolean value)

at ServiceCenter.frmDataLoad.GenerateTicketInfo() in
C:\Projects\Code\Uniway\ServiceCenter\frmDataLoad.vb:line 209



And now for the data in my tables:



scItems:

4 rows.

Two of which have ProblemIDs that each correspond to a different row in the
scProblems table.

Two of which have Null ProblemIDs, simply because there were no problems
with those particular items.

I don't see that table scItems having ProblemId field at all. As a master
table it has not such field. The opposite is true - scProblems has ItemId
field which relates to scItems.ItemId.

scProblems:

2 rows.

Both rows have distinct ProblemIDs



So, the two Null ProblemIDs in the scItems table are obviously causing me
problems. At least thats my assumption. Again, what am I missing?

There should be no ProblemID field at all (and there isn't) in scItems
table.
How do the rows values look like?
Are there any other constraints besided primary keys?

 

[Janis Alksnis]

I got the same error in my applications until I simplified syntax to (in
your case):
dascItemsMain.Fill(dsTicketMain)
I suppose this is because you (and me too) specified table mappings for your
data adapter and it needs the specification of dataset data table no more.