Skip to Content

Create an ID column for an existing table

Nov 13, 2017 at 10:21 AM


avatar image

Hi everyone,

I'm new to HANA, and I currently confront a problem that the table I was imported doesn't have a primary key. I will use the table for creating the view (I used the graphical way to create instead of SQL) and analyzing the data. Since it will be easier for me to analyze with the primary key in the table, I was trying to create an "ID" column by my own.

There are two ways I have already tried,

1) I searched for the Q&A before and got the following SQL
alter table table_name add id numeric(10,0) identity

but somehow I got an error console.

2) I created an calculated column "ID" in HANA view, and set the value to "1" in each column, and used "Rank", sort ascending for the calculated column to generate an sequence for my column, but somehow it still didn't work.
the way I was trying is:

Do anyone know what I did wrong or anyway to create an ID column in the existing table?

Thank you in advanced!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Lars Breddemann
Nov 21, 2017 at 03:47 AM

That's easy!

-- this table has no keys
create column table email_nokey as (

-- add a field for the new key
alter table email_nokey add (UUID varbinary(16));

-- fill with unique values 
update email_nokey set uuid = sysuuid;

-- activate primary key constraint
alter table email_nokey add primary key (uuid);
10 |10000 characters needed characters left characters exceeded