IDENTITY column duplication when using BY DEFAULT parameter

Leszek — Fri, 16 Sep 2022 10:39:39 GMT

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"

Re: IDENTITY column duplication when using BY DEFAULT parameter

dileep_vikram — Thu, 11 May 2023 10:01:59 GMT

Use below alter command to sync the identity column.

alter table table_name change column col_name sync identity

topic IDENTITY column duplication when using BY DEFAULT parameter in Data Engineering

IDENTITY column duplication when using BY DEFAULT parameter

Re: IDENTITY column duplication when using BY DEFAULT parameter