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

Posted on 2005-04-23
Last Modified: 2008-03-06

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.

Question by:joshuadavidlee
    LVL 41

    Expert Comment

    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.

    Author Comment


    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.

    LVL 41

    Accepted Solution

    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...

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK ( for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now