No announcement yet.

Puzzled about how to use Navicat table editing with IDENTITY columns

  • Filter
  • Time
  • Show
Clear All
new posts

  • Puzzled about how to use Navicat table editing with IDENTITY columns

    I've used Navicat for MySQL and SQLite and gotten to used to some flexibility when using Navicat's table editor for tables that have auto-increment / identity columns. In SQL Server and Navicat for SQL Server, things seem to be a little different.

    If I explicitly type a value into the Navicat table editor for the IDENTITY column, it will reject that because I have not explicitly used SET IDENTITY_INSERT [tableName] ON, which can only be done for one table and in the context of a current session. Switching to the SQL query window and typing this before attemtping to add a new record seems cumbersome. It seems like the way it would need to work in Navicat for SQL Server is to check for the IDENTITY column and non NULL value, call SET IDENTITY_INSERT, perform the INSERT with a non NULL IDENTITY, and then turn IDENTITY_INSERT off.

    Conversely, if I try to leave the cell blank in the Navicat table editor, expecting that it will use the NULL to auto-generate the IDENTITY value, I get the 'DEFAULT or NULL are not allowed as explicit identity values' error. In this case, it seems like the way it would need to work is for Navicat to check the IDENTITY column and NULL value, and issue the INSERT statement without the value, i.e. the INSERT statement formed with a NULL causes the error.

    SQL Server Management Studio (SSMS) locks the IDENTITY column for editing if SET IDENTITY INSERT is off, and inserts w/o issue for the NULL case. But I would prefer to use Navicat if possible.

    Any thoughts?

  • #2
    CORRECTION: I see the NULL case works, and a different NOT NULL column was causing the error in that case. So I rephrase to just ask how explicit setting values for identity insert might be possible...


    • #3
      I am sorry that Navicat does not support this feature at this moment. Our development team will consider to improve it in future version.


      • #4
        I would also vote for this feature request. The operation of this kind of update is not seamless at the moment.


        • #5
          Hi mgarwood,

          Thanks for your comment. The request is already in our development list. We will keep improving Navicat in future.