IDENTITY column duplication when using BY DEFAULT parameter
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-16-2022 03:39 AM
Hi, I created delta table with identity column using this syntax:
Id BIGINT GENERATED BY DEFAULT AS IDENTITY
My steps:
1) Created table with Id using syntax above.
2) Added two rows with Id = 1 and Id = 2 (BY DEFAULT allows to do that).
3) Run Insert (without specifying Id column)
My result:
I have duplicated Id's:
Documentation (https://docs.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-create-table-using#parameters) says:
"When ALWAYS is used, you cannot provide your own values for the identity column." - so, I'm using BY DEFAULT because this allows to do that.
Is that suppose to work that way? I thought that identity is identity 🙂
And another quote from docs:
"When you write to the table, and do not provide values for the identity column, it will be automatically assigned a unique and statistically increasing value"
- Labels:
-
Column
-
Identity Column
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-11-2023 03:01 AM
Use below alter command to sync the identity column.
alter table table_name change column col_name sync identity

