topic Re: Issue with identity key column in databricks? in Data Engineering

Issue with identity key column in databricks?

ramankr48 — Thu, 10 Nov 2022 07:28:32 GMT

For the identity key I've used both

GENERATED ALWAYS AS IDENTITY(start with 1 increment by 1) and

GENERATED BY DEFAULT AS IDENTITY(start with 1 increment by 1)

but in both cases, if I'm running my script once then it is fine (identity key is working as it should be) but when I'm running it second time the old one is getting removed and new identity key is being generated.

ex: let's suppose I'm inserting 10 rows in the table and running the script then identity key is being generated from 1 to 10. but second time when I'm running the script identity is generating from 11 to 20.

that's the issue.

I'm also trying to truncate the table but not fruitfull. Using DBR Version 10.4

Re: Issue with identity key column in databricks?

elgeo — Thu, 10 Nov 2022 13:25:20 GMT

Can you share the script you are running?

Re: Issue with identity key column in databricks?

lizou — Wed, 16 Nov 2022 21:25:29 GMT

yes, by default option allow duplicated values per design.

I will avoid this option and use only use GENERATED ALWAYS AS IDENTITY

Using BY DEFAULT option is worse than not using it at all

in BY Default option,

If I forget to set starting value, the ID will be generated from

a lower value and will grow and overlap existing values.👻