HANA Unique constraint Question

[Former Member]

I'm trying to import data from a csv file. While I know I cannot have duplicate data in the primary key. I am getting this error in a text column(ACTIVITYCATEGORYTEXT)  and have no idea why. I know I'm getting the error at this text column because all of my primary keys are unique and when I import the data without this column it comes in fine. I need the data in the ACTIVITYCATEGORYTEXT column to be able to be displayed repeatedly. The Previous data in there is repeated so I don't know why it won't let me upload from my csv. Is there somewhere in HANA where the settings for this column can be altered, or does anyone know why I am getting this error? I attached a screenshot. The longer activitytypeid values are sample csv, while the 400 and below is data I inserted via sql.

[rama_shankar3 · Accepted solution]

Please check and make sure that ACTIVITYCATEGORYTEXT is not defined as a key in your table. Based on your description of the problem you may have the field ACTIVITYCATEGORYTEXT defined as a key.

Drop the data, modify the table to remove ACTIVITYCATEGORYTEXT as the key and then try reloading the CSV file.

good luck

Rama

[Former Member · Accepted solution]

Thanks for your reply Rama. I made sure that ACTIVITYCATEGORYTEXT was not the primary key before. I just dropped the primary key then added it again and still am getting the error. Again when I Load this data without the ACTIVITYCATEGORYTEXT column I don't get the error so I know it must be this column, but cant figure out why I'm getting this error?

..

[Former Member]

So I was able to find a way around the problem. After I created the ACTIVITYTYPE Table I would load some data using SQL Script, then I was trying to import data from a csv and I was getting the unique constraint error for that one particular txt column.

I dropped all the sql data and loaded the csv first and it worked. Then I populated the rest of the table with my sql statement and that worked. I dont know why changing this order made a difference but it did

[Former Member]

Hi Connor,

Before your import csv data to HANA, if you have created corresponding table, then you can alter table column length to cover your data to import successfully by <alter_column_clause>, but you must have related authority for this schema or table.

If you did not create corresponding table, then when you import csv data, you can define corresponding data column type and length for table, SAP HANA will set default type and length, when you import data without existing table, but you need to change type and length at sometimes to cover your data.

Hope this can help you.

Regards,

Jerry

[Former Member]

Hi Jerry,

I am importing the csv to a table which I created and I'm sure that the column length is long enough (40) charachters and it usually less than 10 characters being inserted. My error isn't a size issue its a unique constraint error. 

[former_member184768]

Hi Connor,

Not sure if you already tried, but can you please disable / drop all the constraints on your table (including primary key), import the data and then enable / add the constraints using ALTER TABLE option.

Just an option to check if the error is really because of the constraint violation on the mentioned column.

Regards,

Ravi

[Former Member]

I am sure it is the ACTIVITYCATEGORYTEXT column because when I try to import data without it, it works without this error.

I'm trying to load a CSV into the table I created below and keep getting the unique constraint error for the ACTIVITYCATEGORYTEXT column. So frustrating

CREATE COLUMN TABLE "NIEM_CORE"."ACTIVITYTYPE"(

"ACTIVITYTYPEID" INTEGER NULL,

"ACTIVITYIDENTIFICATION" VARCHAR (40) NULL,

"IDENTIFICATIONTYPE" VARCHAR (40) NULL,

"ACTIVITYCATEGORYTEXT" VARCHAR (40) NULL,

"ACTIVITYDATEREPRESENTATION" TIMESTAMP NULL,

"ACTIVITYDATE" TIMESTAMP NULL,

"ACTIVITYDATERANGE" TIMESTAMP NULL,

"ACTIVITYDESCRIPTIONTEXT" VARCHAR (160) NULL,

"ACTIVITYNAME" VARCHAR (40) NULL,

"ACTIVITYSTATUS" VARCHAR (40) NULL,

"ACTIVITYREASONTEXT" VARCHAR (40) NULL,

"ACTIVITYDISPOSITION" VARCHAR (40) NULL,

"DISPOSITIONTYPE" VARCHAR (40) NULL,

"CASEDISPOSITION" VARCHAR (40) NULL,

"CASEDISPOSITIONTYPE" VARCHAR (40) NULL,

"LANGUAGECODE" VARCHAR (2) NULL,

"LASTMODIFIED" TIMESTAMP NULL,

"UPDATESOURCE" VARCHAR (40) NULL);

[former_member184768]

Thanks..  is it possible for you to upload the text / csv file also ?

Regards,

Ravi

[rindia]

Hi Connor,

If you can send your sample excel sheet then I will upload and see.

Also let me know how many fields to want to have key.

This will be the easiest way to re-create your scenario.

Regards

Raj

[Former Member]

Okay, can I email you the files?

Thanks

[rindia]

Please send the excel file to my email rajkumar.saap at gmail.com I will upload into HANA and will see the problem.

Regards

Raj