topic Re: Unity Catalog Primary key column taking duplicates in Data Engineering

Unity Catalog Primary key column taking duplicates

AkasBala — Sun, 14 May 2023 06:41:13 GMT

I have Updated a Hive Meta Store from a Unity Catalog. I have setup Primary keys on the table. When I try to insert duplicates its succeeding Inserts and seems like PK is not working. Anyone else seeing such behaviour ?

Re: Unity Catalog Primary key column taking duplicates

AkasBala — Mon, 15 May 2023 17:03:40 GMT

@Debayan Mukherjee Im able to insert the same record where we have Primary key setup on the table.

Re: Unity Catalog Primary key column taking duplicates

AkasBala — Mon, 15 May 2023 17:13:44 GMT

-----------------

ALTER TABLE ccietlfindev.finsilver.companyperiods ALTER COLUMN iCompanyId SET NOT NULL;

ALTER TABLE ccietlfindev.finsilver.companyperiods ALTER COLUMN iPeriodId SET NOT NULL

ALTER TABLE ccietlfindev.finsilver.companyperiods ADD CONSTRAINT companyperiods_pk PRIMARY KEY(iCompanyId,iPeriodId);

select iCompanyId,iPeriodId, count(1) from ccietlfindev.finsilver.companyperiods

group by iCompanyId,iPeriodId

having count(1) > 1

insert into table ccietlfindev.finsilver.companyperiods

values ('1','182','1996-08-31 23:59:59.998','Aug 1998','1996-08-31 00:00:00.000','C','2023-05-11 15:01:43.707')

Select * from ccietlfindev.finsilver.companyperiods

where iCompanyId = '1' and iPeriodId='182'

delete from ccietlfindev.finsilver.companyperiods

where iCompanyId = '1' and iPeriodId='182' and PeriodDesc='Aug 1997'

Select * from companyperiods

In the case when I run count after the second record insert, I see 2 records instead of just one and should have failed saying "you are trying to insert duplicate records where there is a primary key setup"

@Debayan Mukherjee

Re: Unity Catalog Primary key column taking duplicates

AkasBala — Wed, 17 May 2023 13:25:00 GMT

@Debayan Mukherjee Any info on the above plz ??

Re: Unity Catalog Primary key column taking duplicates

Debayan — Mon, 15 May 2023 16:38:42 GMT

Hi, When you say PK is not working, is there any error surfaced?

Please tag @Debayan with your next comment so that I will get notified. Thank you!