Rectangle 27 0

Add a auto increment primary key to existing table in oracle?


Step 1. - Create a Relational Diagram

Add the entity (table) that required to have auto incremented PK, select the type of the PK as Integer.

Additional information relating to the auto increment can be specified by selecting the 'Auto Increment' tab.

Click OK (Apply) to the Column Properties dialog box.

Click OK (Apply) to the Table Properties dialog box.

Get the properties of the PK column. You can double click the name of the column or click on the 'Properties' button.

It is usually a good idea to mention the sequence name, so that it will be useful in PL/SQL.

Select the General Tab (Default Selection for the first time). Then select both the 'Auto Increment' and 'Identity Column' check boxes.

Table appears in the Relational Diagram.

You can first create a Logical Diagram and Engineer to create the Relational Diagram or you can straightaway create the Relational Diagram.

You can use the Oracle Data Modeler to create auto incrementing surrogate keys.

Note
Rectangle 27 0

Add a auto increment primary key to existing table in oracle?


CREATE SEQUENCE SEQ_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 99999999
MINVALUE 1
NOCYCLE;
UPDATE tableName SET columnName = seq_test_id.NEXTVAL
alter table tableName add(columnName NUMBER);

Anyway, if that is the case, Outside of dropping and re-creating the table, in oracle the answer is no. Typically the order of the columns is irrelevant. If you require the columns be in a specific order, I would suggest creating a view of the table with the columns in the order you want.

Do you mean you want the column to be the first column in the table rather than the end of the table?

Then create a sequence:

Yeah, like I said before, your options in oracle are to drop and re-create the table (rename table, create new table in the right order, reinsert to new table)...or to just simply create a view that selects from your table in the order you want.

and, the use update to insert values in column like this

can i change column id of new column

yes exactly i want to add this pk as row id 1

Note
Rectangle 27 0

Add a auto increment primary key to existing table in oracle?


ALTER TABLE table_name
  ADD CONSTRAINT pk_table_name PRIMARY KEY( new_pk_column )
CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT sequence_name.nextval
    INTO :new.new_pk_column
    FROM dual;
END;
UPDATE table_name
   SET new_pk_column = sequence_name.nextval;

@mallikarjun - I'm having a hard time understanding what it is you're asking. What, exactly, is someone doing? What, exactly, is the error?

@mallikarjun - What does "column id" mean? What, exactly, are you trying to change? Are you trying to change the order of columns in a table?

If you are on an older version of Oracle, the syntax is a bit more cumbersome

If you have the column and the sequence, you first need to populate a new key for all the existing rows. Assuming you don't care which key is assigned to which row

If you want to generate the key automatically, you'd need to add a trigger

Once that's done, you can create the primary key constraint (this assumes that either there is no existing primary key constraint or that you have already dropped the existing primary key constraint)

The second trigger query (SELECT INTO) is necessary for 10gR2

i have done the above code and it works fine. but the column id value is changed the last inserted column got last column id. but in case if some of our colleagues insert data into table they start with inserting primary key column value then it will show error. Is there any way to change the column id.

Note
Rectangle 27 0

Add a auto increment primary key to existing table in oracle?


Step 1. - Create a Relational Diagram

Add the entity (table) that required to have auto incremented PK, select the type of the PK as Integer.

Additional information relating to the auto increment can be specified by selecting the 'Auto Increment' tab.

Click OK (Apply) to the Column Properties dialog box.

Click OK (Apply) to the Table Properties dialog box.

Get the properties of the PK column. You can double click the name of the column or click on the 'Properties' button.

It is usually a good idea to mention the sequence name, so that it will be useful in PL/SQL.

Select the General Tab (Default Selection for the first time). Then select both the 'Auto Increment' and 'Identity Column' check boxes.

Table appears in the Relational Diagram.

You can first create a Logical Diagram and Engineer to create the Relational Diagram or you can straightaway create the Relational Diagram.

You can use the Oracle Data Modeler to create auto incrementing surrogate keys.

Note
Rectangle 27 0

Add a auto increment primary key to existing table in oracle?


ALTER TABLE table_name
  ADD CONSTRAINT pk_table_name PRIMARY KEY( new_pk_column )
CREATE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT sequence_name.nextval
    INTO :new.new_pk_column
    FROM dual;
END;
UPDATE table_name
   SET new_pk_column = sequence_name.nextval;

@mallikarjun - I'm having a hard time understanding what it is you're asking. What, exactly, is someone doing? What, exactly, is the error?

@mallikarjun - What does "column id" mean? What, exactly, are you trying to change? Are you trying to change the order of columns in a table?

If you are on an older version of Oracle, the syntax is a bit more cumbersome

If you have the column and the sequence, you first need to populate a new key for all the existing rows. Assuming you don't care which key is assigned to which row

If you want to generate the key automatically, you'd need to add a trigger

Once that's done, you can create the primary key constraint (this assumes that either there is no existing primary key constraint or that you have already dropped the existing primary key constraint)

The second trigger query (SELECT INTO) is necessary for 10gR2

i have done the above code and it works fine. but the column id value is changed the last inserted column got last column id. but in case if some of our colleagues insert data into table they start with inserting primary key column value then it will show error. Is there any way to change the column id.

Note
Rectangle 27 0

Add a auto increment primary key to existing table in oracle?


create sequence t1_seq start with 1 increment by 1 nomaxvalue;
create trigger t1_trigger
before insert on t1
for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

Had to take the sequence from Hermit's answer.

Say your table is called t1 and your primary-key is called id First, create the sequence:

Then create a trigger that increments upon insert:

There's no need to select into in 11g, you can assign the sequence value directly to :new.id.

can i change column id of new column

yes, that's exactly what :new.id is doing

Note
Rectangle 27 0

Add a auto increment primary key to existing table in oracle?


create sequence t1_seq start with 1 increment by 1 nomaxvalue;
create trigger t1_trigger
before insert on t1
for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

Had to take the sequence from Hermit's answer.

Say your table is called t1 and your primary-key is called id First, create the sequence:

Then create a trigger that increments upon insert:

There's no need to select into in 11g, you can assign the sequence value directly to :new.id.

can i change column id of new column

yes, that's exactly what :new.id is doing

Note
Rectangle 27 0

Add a auto increment primary key to existing table in oracle?


CREATE SEQUENCE SEQ_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 99999999
MINVALUE 1
NOCYCLE;
UPDATE tableName SET columnName = seq_test_id.NEXTVAL
alter table tableName add(columnName NUMBER);

Anyway, if that is the case, Outside of dropping and re-creating the table, in oracle the answer is no. Typically the order of the columns is irrelevant. If you require the columns be in a specific order, I would suggest creating a view of the table with the columns in the order you want.

Do you mean you want the column to be the first column in the table rather than the end of the table?

Then create a sequence:

Yeah, like I said before, your options in oracle are to drop and re-create the table (rename table, create new table in the right order, reinsert to new table)...or to just simply create a view that selects from your table in the order you want.

and, the use update to insert values in column like this

can i change column id of new column

yes exactly i want to add this pk as row id 1

Note