cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Unity Catalog Primary key column taking duplicates

AkasBala
New Contributor III

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 ?

4 REPLIES 4

Debayan
Esteemed Contributor III
Esteemed Contributor III

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!

AkasBala
New Contributor III

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

AkasBala
New Contributor III

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

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โ€‹ 

AkasBala
New Contributor III

@Debayan Mukherjeeโ€‹ Any info on the above plz ??

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.