Menu
I want to add a new auto increment primary column to a existing table which has data. How do I do that?
I first added a column and then try to add a sequence after that, I lost how to insert and make that column as primary key.
560
To add a new AUTO_INCREMENT integer column named c : ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c); We indexed c (as a PRIMARY KEY ) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL .
You can double click the name of the column or click on the 'Properties' button. Column Properties dialog box appears. Select the General Tab (Default Selection for the first time). Then select both the 'Auto Increment' and 'Identity Column' check boxes.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
To change a primary key to auto_increment, you can use MODIFY command. Let us first create a table. Look at the above sample output, StudentId column has been changed to auto_increment.
Say your table is called t1 and your primary-key is called id
First, create the sequence:
create sequence t1_seq start with 1 increment by 1 nomaxvalue; Then create a trigger that increments upon insert:
create trigger t1_trigger before insert on t1 for each row begin select t1_seq.nextval into :new.id from dual; end; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
