how to have nullable columns in primary key

MuniBrokers

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

kthorngren

What dataabase are you using?

Does it support nullable primary keys?

How does this relate to Datatables?

Kevin

MuniBrokers

Editor::inst($db, 'MSBW_TBL', array( 'NAME_ABBR', 'TRADER',     'BROKER',    'STATE', 'BOND_TYPE'))                             
    ->fields(
        Field::inst(    'NAME_ABBR'      ),
        Field::inst(    'TRADER'         ),
        Field::inst(    'BROKER'         ),
        Field::inst(    'STATE'          ),
        Field::inst(    'BOND_TYPE'      ),
        Field::inst(    'NOT_TRADER'     ),
        Field::inst(    'NOT_STATE'      ),
        Field::inst(    'NOT_BOND_TYPE'  ),
        Field::inst(    'PAR'            ), 
        Field::inst(    'MAX_PAR'        ),
        Field::inst(    'MAT_ALL'        ),
        Field::inst(    'MAT_START'      ), //-> validator( Validate::dateFormat('mm/dd/YYYY')),
        Field::inst(    'MAT_END'        ), //-> validator( Validate::dateFormat('mm/dd/YYYY')),
        Field::inst(    'YEAR_CT_ST'     ),
        Field::inst(    'YEAR_CT_END'    ),
        Field::inst(    'ACTION'         ),
        Field::inst(    'ALLOCATION'     )
    )

error message is:
DataTables warning: table id=msbw_tbl - Primary key element is not available in data set.

BOND_TYPE is null which is a possible value for it, if I remove BOND_TYPE the data is returned correctly. I need columns in the primary key to be nullable, the key will still be unique.

this works in sqldeveloper with no problem, I read in the documentation that dataTables automatically set an attribute (called allowDBNullable) to false for all columns in the primary key, but there is no way to reset it to true that I can find.

help

allan

With our libraries you can't do that I'm afraid. There must be a way to uniquely identify every row in the table. If there wasn't, and you edited a null pkey row, it wouldn't be able to determine which of the rows which are null pkey'ed that you want to update.

Or are you saying that only a single row can be null pkey'ed? Even then, I don't believe this is something our libraries would allow for.

Allan

MuniBrokers

Thank you allan for your comments, the problem is that the first four columns of the primary key ALWAYS have a value that is unique, the succeeding 4 columns that are part of the unique index will be null for the main row, any additional row with the same first four columns will have a value in at least one (or more) of those remaining four columns.
This works fine in Oracle using SQLdevelper, it works fine in using a Jqgrid (php) program, but in dataTables, it will NOT let any of those last four columns be NULL.

I read in the blogs/forum that dataTables automatically sets an attribute (called allowDBNull, I think) on primary key columns to be false. There doesn't seem to be any documentation on how to reset it back to true.

There are many programs that use the columns together in evaluations, so changing all of them to test for a blank, or such would be horrific, there is no reason why a column (or more) in a primary key should be restricted to not be nullable.

we want all of our tables to be updated via dataTables, rather than individuall jqGrid php's, I am at a loss as how to resolve this issue.

allan

Thanks for the explanation. I think it is this line that is causing you problems.

If you replace that (in your local copy) with:

if ( $val === null ) {
  $val = 'null';
}

is it then happier?

I read in the blogs/forum that dataTables automatically sets an attribute (called allowDBNull, I think) on primary key columns to be false.

I don't think so. This is our Oracle specific code and that attribute isn't specified.

I think, particularly from the error message, that it is a limitation (/assumption) in our libraries.

Allan