VARCAHR id wont update table on create

VARCAHR id wont update table on create

we0038we0038 Posts: 39Questions: 13Answers: 1

Hi, I would like to confirm this behavior please.
I have a simple table with PRIMARY key as VARCHAR. When creating a new record the table does not update automatically as expected. After a lot of reading and debugging, one solution that doesn't need using event with table.ajax.reload() is to make the id as INTEGER. Is this intentional?

Answers

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    When creating a new record the table does not update automatically as expected.

    I'm not sure what you mean by that. Are you expecting a new primary key to be allocated "automatically"?
    A varchar field is not a good choice for primary key. Integers may be auto-incremented, varchars may not.

  • we0038we0038 Posts: 39Questions: 13Answers: 1

    I am not expecting auto increment. this is a special case table.
    say table has
    id varchar(5) (primary, unique, not null, and has index)
    value varchar(255)

    when creating a new record both values will be entered manually. however after submit success the table will NOT show the newly entered row. an ajax reload is needed OR the primary key has to be integer.

    I just want to confirm that my findings/understanding are correct.

    Thanks

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited August 2020

    Is this with Editor? If yes then Editor expects the created row as the response then will update the Datatable. See this doc. Is your server script responding with the created record?

    If no then please show us what you are doing.

    Kevin

  • we0038we0038 Posts: 39Questions: 13Answers: 1

    Hey Kevin,

    Yes I am talking about Editor.

    consider this live example
    http://live.datatables.net/suvohazo/1/edit

    Try to create a new record (table will not update).
    Refresh the page (you will see that record)

    my test table:

    CREATE TABLE `id_varchar` (
      `id` varchar(10) NOT NULL PRIMARY KEY,
      `value` varchar(10) NOT NULL
    );
    

    my test backend:

    Editor::inst( $db, 'id_varchar' )
        ->fields(
          Field::inst( 'id_varchar.id' ),
          Field::inst( 'id_varchar.value' )
        )
        ->process( $_POST )
        ->json();
    

    while I was making this demo, I figured out why this is happening. Table name prefix seems to be an issue with tables that do not have integer ID.

    so this should be fine

    Editor::inst( $db, 'id_varchar' )
        ->fields(
          Field::inst( 'id' ),
          Field::inst( 'value' )
        )
        ->process( $_POST )
        ->json();
    

    or make an integer ID for previous example (which is my current workaround).

    In my actual case, using table name prefix is needed due to multiple joins.

This discussion has been closed.