Bug report #15218
MSSQL columns with default values not ignored
|Affected QGIS version:||master||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||23155|
Many MSSQL tables use unique identifiers (UUID) for columns. These often have a default value such as newid(). QGIS currently attempts to overwrite these values when saving which results in an error.
QGIS needs to completely ignore MSSQL columns that have a default value when doing updates and inserts. Setting the fields to "Hidden" works for inserts but then fails for updates.
#1 Updated by Damien Smith about 5 years ago
Actually, setting to "Hidden" or "Text" (Not editable) allows inserting and updating but copying and pasting fails if there is a unique constraint because the UUID is duplicated rather than using the default newid() function.
So, the only way to fix this is to reset fields which have a default value to their default value when copying and pasting. Also, using a view is not an option for large MSSQL tables as QGIS does a full table extents scan on views as it's not aware of the spatial index on the base table.