Strongly Typed DataSet with Foreign Key Relation missing the foreign key in the rows


I have a strongly typed dataset that i have drawn in my project and it must remain strongly typed.
It has a product table with a primary key.  It also has a column with a foreign key called DescriptionID which points to a DescriptionID in the Descriptions table because mutiple products could have the same description in my project.  In the dataset schema designer i have draged and dropped this relation and defined it and it all works and is fine and tested out well.  So I think I have described the very simplest standard relation.  

The issue is when the Products table references a DescriptionID that does not exist in the Descriptions table however this might have happened.  Perhaps the DescriptionID existed at some time but it was erased later by accident or some other reason.

When i use an sqldataadapter to fill the dataset Products table it throws an error "failed constraints etc etc etc" because a DescriptionID is being referenced that does not exist.  If i fix the row so that it references a DescriptionID that does exist all works smoothly.  However life is not perfect and I need to know what to do to stop the page from failing if a DescriptionID is missing when using a strongly typed dataset.

Who is Participating?
grayeConnect With a Mentor Commented:
From the DataSet GUI... right click on the relationship "thread" that connects the two tables.   Select "Edit Relation"... at the bottom you'll see 3 boxes... (Update, Delete, Accept/Reject).   Change all of those properties to None.

After you've done this... you will get null values on related records where the foriegn key is missing.  You can detect this condition in your code and disaply the message you've described.

BTW: I never use the DataSet GUI...
Well, let's start at the beginning...  The whole point of "referential integrity" is to *prevent* the database inconsistancies that you're describing.   Most folks apply referential integrity at the database layer (to prevent accidential corruption when messing with the database directly... outside your application).

Have said all that... if your underlying database does not have referential integrity "turned on", then you should not use referential integrity at the the application layer.

Does that make sense?  Your application is trying to apply a "higher standard" to the database than what the database itself is designed to support.

So, your options are:
1) fix the underlying database, turn on referential integrity at the database layer
2) at the application layer, turn off referential integrity

Option 1 would be the overall best option (but you might not have complete control over the database).

When you add a Relation to the DataSet.Relations collection, it default is to also add a Constraint to the Constraints collection.   It's the job of the Contraint (not the Relation) to enforce referential integrity.   So, all you'd have to do is remove the Constraint or never build the Constraint do begin with.

The contructor for the Relation object has a 4th parameter, that if set to False, will build the Relation without building the Constraint.
joshuadavidleeAuthor Commented:

i understand all you said and agree but I want to do this anyways.  If I am dragging and dropping a relation I get a wizard that defines the relation.  How do I set this 4th paramter to false in the wizard since it will be strong typed?  which button or toggle or drop down is it in the wizard?

and also in sql manager where do i turn on the referential integrety in my databse?  I will set that also and experiment with it.

my eventual plan here is to leave a message in the datagrid display in the proper cell saying that they have referenced soemthing that does not exist and please reselect a proper item.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.