add data into primary key

Question

this is my table.In this table i want to add a primary key column name "emp_id" as the first column .I don't know how to do it .So,can you please help me!

EMP_NAME             EMP_POS            SALARY           GENDER 
----------------- ----------------- --------------      ------ 
anand              worker             10000                 M      
balu               manager            50000                 M      
carl               manager            50000                 M      
riya               md                 60000                 F      
prabhu             owner              99999999              M

Hint: Alter command to add a column and then Update with Rownum to populate the data into that column. — Ankit Bajpai, Dec 26 '17 at 14:18
hey,its only there about create table in the link. but i need to add column to an existing table and it should be primary key. how to do that? — prabhu r, Dec 26 '17 at 16:20

score 0 · Accepted Answer · answered Dec 26 '17 at 19:06

The old way of doing this is a multi-step process:

add the column which will be the primary key
update the column
enforce the primary key.

Something like this:

create sequence t23_id;

alter table t23 add id number;

update t23 
set id = t23_id.nextval
;

alter table t23 add constraint t23_pk primary key (id);

In 12c Oracle added Identity columns (like SQL Server auto-incrementing columns). This reduces the number of steps to two:

alter table t23i add id number GENERATED ALWAYS AS IDENTITY primary key;

alter table t23i add constraint t23i_pk primary key (id);

Unfortunately it can't be done in one step. This ...

alter table t23i add id number GENERATED ALWAYS AS IDENTITY primary key;

...hurls ...

ORA-01758: table must be empty to add mandatory (NOT NULL) column

Livesql demo

score -1 · Answer 2 · edited Dec 27 '17 at 14:59

-1

Introduce identity column:
see http://sql-plsql.blogspot.sg/2014/11/add-identity-column-to-table.html

Reorder the columns:
see http://www.dba-oracle.com/t_change_column_order_within_oracle_table.htm

Hope these references help.

edited Dec 27 '17 at 14:59

marc_s

732,580
175
1,330
1,459

answered Dec 26 '17 at 15:07

nickedeye

154
4

add data into primary key

2 Answers2