I am working on a small web application and using Microsoft SQL for the data storage. I am interfacing with the database using a service layer that leverages JDBC.
Currently when I try to do a PUT for a "Customer" that does not exist by ID into the database, I keep on getting an error:
Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.
In order to support a proper RESTful API, I would like to be able to Insert this ID if it doesn't exist or update an already existing customer. I've looked up a myriad of issues somewhat related to mine, and a lot of them cautiously suggest turning IDENTITY_INSERT on, but say it is a bad practice. I'm curious what I can do database or in-code to healthily support RESTful PUTs - and if there is a way to do it while maintaining an IDENTITY column so I can do POST operations without having knowledge of the CustomerID column.
If you are using SQL Server 2012 or later, you can use sequences. This will avoid the need for identity insert. https://msdn.microsoft.com/en-us/library/ff878058.aspx
Whichever technique you use, you will need to partition the key range so that you are not supplying keys that will overlap with generated keys, as SQL Server has no knowledge of what keys you have supplied manually.
To do this with an identity column, you can define the column as identity(1000000,1) so that the lower million keys are available for manual insertion. If you use an identity column, you will have to use identity_insert to manually supply a value, so sequences are preferable.