none
How to define a field as NOT a primary key RRS feed

  • Question

  •  

    I have a data adapter linked to a stored procedure that uses a LEFT JOIN to get data from a couple of tables.  One of the fields in the right table is the primary key, but if there's no data there I want to return zero.  As such I use a COALESCE on that field:

     

    COALESCE(InspectionDataID,0) AS InspectionDataID.

     

    This works fine as a stand-alone stored procedure, but in my dataset it insists on regarding this field as a primary key to the datatable, even though I thought I had "decoupled" it by using COALESCE.

     

    So, when I try to run my application, I get a "failed to enable constraints" error because several of the fields are zero.

     

    I even tried writing a user-defined function that takes the primary key as an argument and returns zero if it's null.  I thought that might effectively "decouple" it, but no -- the dataset still regards it as the primary key!  Because of that, I can't set the "unique" property to false in the dataset.

     

    Is there any way I can get it to regard this field as just another field?  (Short of going into the schema and manually setting the unique constraint to false -- I don't really want to be fiddling with the automatically-generated code if I can avoid it.)

    Saturday, April 5, 2008 12:41 AM

Answers

  • Anyway, the answer to my original question (how to decouple a field in a dataset from its status as a unique primary key) is to change the returned name of the field so it's not the same as the primary key in the table.

     

    As soon as I put

     

    COALESCE(InspectionDataID, 0) AS dataID

     

    instead of

     

    COALESCE(InspectionDataID, 0) AS InspectionDataID

     

    it appeared in the DataTable as just an ordinary integer field, not a primary key.

     

    It seems really strange to me that the name should make all the difference, but it does.

     

    EDIT: I discovered that you also have to do something to make the field a computed column (like COALESCE). Just saying InspectionDataID AS dataID does not do it.

    Wednesday, April 9, 2008 4:21 PM

All replies

  • So if I'm understanding this correctly, your base query looks something like:

     

       SELECT <some columns>, COALESCE(InspectionDataID, 0) AS InspectionDataID

       FROM SomeTable

       LEFT JOIN OtherTable on SomeTable.InspectionDataID = OtherTable.InspectionDataID

     

    Do I have that right?

     

    If so, the "failed to enable constraints" property isn't happening because of the primary key constraint on SomeTable, it's failing because of the foreign-key constraint in the DataRelation connecting the two tables.  There's no value in OtherTable for the SomeTable rows that have a 0 in that column, and so you can't enable a foreign key constraint, because there are rows in the parent table that have no corresponding rows in the child table.

     

    There are many different things you can do.  I can think of five:

    1. Turn EnforceConstraints off.  This is almost certainly the wrong thing to do.
    2. Add a row to OtherTable that has a primary key of 0.
    3. Allow InspectionDataID to be NULL instead of forcing its value to be 0.  That's how every other foreign key column in the universe works, why shouldn't yours?
    4. Change your column list to be something like "COALESCE(InspectionDataID, 0) AS InspectionDataID, InspectionDataID AS RealInspectionDataID", and let the designer create the relationship using the right way.
    5. Drop the COALESCE from your query, and in your DataTable, add a DataColumn whose Expression returns either the InspectionDataID or 0.
    Monday, April 7, 2008 8:20 PM
  • Thanks for your reply Robert!

     

    You are exactly right about the query.

     

    As for your suggested solutions:

     

    (1) Don't want to do that, you're right

    (2) Don't think I can do that, as it's identity column with start of 1

    (3) Allowing InspectionDataID to pass through as null: the problem I have with that is, the data adapter is set up to throw an exception if there is a null value on a numeric column.  There doesn't seem to be anything I can do about it. For numeric data, you can't set the action to anything but "throw exception".  The exception occurs before I even get the data.  I can't intercept it before that, or at least I don't know how.

    (4) I can give that one a try

    (5) As I say, I really can't drop the COALESCE or an exception gets thrown.  I just can't let anything NULL pass through.  As for adding a DataColumn that returns InspectionID or 0 -- could you give an example of how to do that?  I'm not sure what you mean.

     

    The way I actually got this to work is, I created a table variable in my stored procedure and inserted the entire OtherTable into it.  Then I did the LEFT JOIN on this temporary table.  Even then, it didn't work when I said COALESCE(ItemIDFromTempTable,0) AS InspectionDataID.  As long as the name was InspectionDataID, the dataset still interpreted it as a primary key. I had to change the name to dataID, then I was finally able to decouple it from the constraints.

     

    Thanks again for your response.  If you know how to pass a numeric value through as a null, I'm all ears -- I can't get it to work.

    Tuesday, April 8, 2008 2:57 AM
  • Actually, I am wrong in saying the query was just like your example.  That's not quite true.

     

    Here's a simplified version of the query:

     

     SELECT
           SSIA.InspectionItemID,
           SSIA.InspectionItemNumber,
           COALESCE(SSIAT.InspectionArea, '') AS InspectionArea,
           COALESCE(SSID.InspectionDataID,0) AS InspectionDataID
     FROM
      (
           SchoolSiteInspectionAreas SSIA
           LEFT JOIN
           SchoolSiteInspectionAreaTranslations SSIAT
           ON
           SSIAT.InspectionItemID = SSIA.InspectionItemID
           AND
           SSIAT.lang = @lang
      )
      LEFT JOIN
      SchoolSiteInspectionData SSID
      ON
      SSID.InspectionItemID = SSIA.InspectionItemID
      AND
      SSID.rid = @rid


     

    The line in blue is the problematic line.

     

    So, it's not joining on InspectionDataID, it's joining on InspectionItemID. InspectionDataID is the primary key of SSID.  InspectionItemID is the primary key of SSIA, and a foreign key in SSID.

    Tuesday, April 8, 2008 3:24 AM
  • What do you mean by "the data adapter is set up to throw an exception if there is a null value on a numeric column"?  Why can't you just set AllowDBNull to true for the column?  It's clearly nullable in your database.  What are you getting from making it non-nullable in your DataSet?

    Tuesday, April 8, 2008 5:58 PM
  • I'm not at work, so I can't look at the code, but I'm pretty sure that AllowDBNull IS set to true.

     

    Anyway, that's not the problem.  It doesn't throw an exception because the value is DBNull and AllowDBNull is false.  It's throwing an exception because, for a numeric value, it won't let you set the NullValue property to anything BUT (Throw exception). If you try to set it to a different value (like Null) you get a "property value is not valid" window, which states: "For columns not defined as System.String, the only valid value is (Throw exception)."

     

    Therefore, if the value is null and it is numeric, it throws an exception even if AllowDBNull is true.  This happens inside the automatically-generated DataSet code, and my code never gets the DataSet.

     

    This is why for ALL my stored procedures that return numbers, I have COALESCE statements to return zeroes in the case of a null value.

    Wednesday, April 9, 2008 2:58 AM
  • Anyway, the answer to my original question (how to decouple a field in a dataset from its status as a unique primary key) is to change the returned name of the field so it's not the same as the primary key in the table.

     

    As soon as I put

     

    COALESCE(InspectionDataID, 0) AS dataID

     

    instead of

     

    COALESCE(InspectionDataID, 0) AS InspectionDataID

     

    it appeared in the DataTable as just an ordinary integer field, not a primary key.

     

    It seems really strange to me that the name should make all the difference, but it does.

     

    EDIT: I discovered that you also have to do something to make the field a computed column (like COALESCE). Just saying InspectionDataID AS dataID does not do it.

    Wednesday, April 9, 2008 4:21 PM
  •  cedubose wrote:

    Anyway, that's not the problem.  It doesn't throw an exception because the value is DBNull and AllowDBNull is false.  It's throwing an exception because, for a numeric value, it won't let you set the NullValue property to anything BUT (Throw exception). If you try to set it to a different value (like Null) you get a "property value is not valid" window, which states: "For columns not defined as System.String, the only valid value is (Throw exception)."

     

    Okay, I think I see where the confusion is coming from.  You're running up against an aspect of the DataSet Designer that really ought to be better documented than it is.

     

    You can certainly have nullable numeric columns in ADO.  (In fact, if you look at the help page for AllowDBNull, the example uses a System.Int32 column.)  This code works perfectly:

     

    Code Snippet

    DataTable t = new DataTable();

    DataColumn c = t.Columns.Add("num", typeof(System.Int32));

    c.AllowDBNull = true;

    DataRow r = t.NewRow();

    r["num"] = System.DBNull.Value;

    t.Rows.Add(r);

     

    If you create a table with a nullable column in the DataSet designer, the NullValue property tells the designer what you want the generated column accessor property to do if the underlying column contains System.DBNull.Value.  For string columns, you can make it return String.Empty, or throw an exception.  For numeric columns, you can only make it throw an exception.

     

    But that only governs the way the column accessor works.  It doesn't govern whether or not the column itself can contain null values. 

     

    So how do you deal with null values?  For any nullable column named Foo, the generator will create two methods in the strongly-typed DataRow class:  SetFooNull() and IsFooNull().  If row is a strongly-typed DataRow with a nullable int column named Num, any time row.Num evaluates, it'll throw an exception.  You have to use the row.IsNumNull() method to avoid this.  So instead of writing this:

     

    Code Snippet

    int num = row.Num;

     

    you have to write this:

     

    Code Snippet

    int? num = null;

    if (!row.IsNumNull()) num = row.Num;

     

    or, more succinctly (and my code is full of this particular construction):

     

    Code Snippet

    int? num = (row.IsNumNull()) ? null : row.Num;

     

    And to go in the other direction:

     

    Code Snippet

    if (num == null)

    {

       row.SetNumNull();

    }

    else

    {

       row.Num = num;

    }

     

    This is unwieldy, especially if you have to retrofit existing code to use it.  But I think it's much better than using a magic number to represent NULL, which is essentially what you're doing by forcing the column's value to 0.

     

    (Also:  you can use ISNULL(x, y) instead of COALESCE(x, y)).

    Thursday, April 10, 2008 7:48 PM
  • Thanks for your input Robert.

     

    But ... the problem is, I never can get to the point where I can deal with the rows in that way.  It fails when it tries to access the data.

     

    Typically I use the GetData function that returns a strongly typed DataTable, as in

     

    Code Snippet
    MyDataTable tbl = MyDataAdapter.GetData(....);

     

     

     

    It's at this point that it fails.  I never get to the point where I can loop through the rows.

     

    Am I missing something here?

    Saturday, April 12, 2008 1:58 AM
  • Something's not right in your DataSet configuration.  I can (and just did, to verify that I'm not just flapping my gums here) create a table with a nullable numeric column in a database, drag the table into the DataSet designer, and call GetData to return the strongly-typed table, and the nullable column causes no trouble at all.  I bet you can too, if you create a standalone project and database to test with. 

     

    I urge you to dig into this and figure out what's really going on.  In my experience (and I regret to say it's considerable), working around apparent problems in the DataSet designer without understanding what's really going on leads to a whole freaking lot of rework.  I'm happy to help however I can.

    Monday, April 14, 2008 8:50 AM
  • Could you try it exactly the way I did it?  That might be more useful in figuring out what I'm doing wrong.  The process you're describing is quite different from what I did.  I did not drag a table onto the DataSet.  I used a stored procedure.

     

    What I did was:  I added a DataSet to the project.  In the data set, I added a new TableAdapter. It came up with a wizard, and I went through the wizard and set the connection string, etc.

     

    When it came to the question where it asked me "How should the TableAdapter access the database", I selected "Use existing stored procedures."  I then selected the stored procedure I wanted from a dropdown list, and it created the TableAdapter with strongly-typed DataTable.

     

    If you could do it this way, and use a stored procedure that returns an integer that has a null value, and then try to call that procedure in code, I'd really be interested to know what results you get.

     

    (If it's relevant, the project that I added the DataSet to (my data access layer) is a library project, which I then add as a reference in my application.)

    Tuesday, April 15, 2008 5:05 PM
  • What I did:

    1. Created a database table called test with a nullable int column called num.
    2. Created a stored procedure that does SELECT id, description, num FROM test.
    3. Created a TableAdapter that used this stored procedure as its SelectCommand.

    The DataTabe it generated has a column called num, an IsnumNull() method, etc.  When I call GetData(), the DataTable returned contains DBNull in the num column (for those rows in which it's actually NULL).

     

    What are you doing that's different?
    Wednesday, April 16, 2008 3:19 AM
  • Well, you're right.  I did a test and it is allowing me to have a number that's null.

     

    Not sure why I thought it was failing on that.  It must have been coming up with a different error, and I mistakenly thought it was because the number was null.  I can't seem to recreate the error now.

     

    In this particular case, though, I do need to return a zero if the dataID is null, so the COALESCE (or ISNULL) is the right thing to use.  In this particular case, the problem was that it was making the field in the dataset a primary key, even though I was doing a LEFT JOIN and so sometimes it was coming back non-unique, and it was complaining about that.  However, I fixed that by giving the field a name that was different from the name of the field in the database.

     

    Thanks for helping me with this!  What a relief to know I don't have to use COALESCE statements all over the place.

     

    Wednesday, April 16, 2008 9:47 PM