Skip to Content

Create an ID column for an existing table

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: http://www.saphanacentral.com/2016/08/how-to-generate-row-number-or-sequence.html

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

Thank you in advanced!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 21, 2017 at 03:47 AM

    That's easy!

    -- this table has no keys
    create column table email_nokey as (
        select EMAILADDRESS, EMAIL_MIRR from "DEVDUDE"."EMAIL_ADR");    
    
    -- 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);
    
    Add comment
    10|10000 characters needed characters exceeded