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
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.