Search code examples
mysqlsqlddl

How to add new column in table as primary key?

I wonder how to add new column ( set as primary key and set default value) in existing table ? I tried 

ALTER TABLE table_name ADD ( column_name VARCHAR (10));
ALTER TABLE table_name  ALTER COLUMN column_name SET DEFAULT 'value1';
ALTER TABLE table_name ADD PRIMARY KEY(column_name);
>> ERROR 1138 (22004): Invalid use of NULL value

I saw couple posts but it requires to delete all existing data in the table which I don't want to. Is there other way to add new column as primary key without delete data in table?

My current table:

Current table

My new table that I want to create:

new table

Thanks

Solution

  • Doing this gives ERROR since whenever you add a new column in a table which already has 1 or more rows then the new column will get NULL values in all of its tuples which is contradictory to the rule which says PRIMARY KEY CAN NOT CONTAIN NULL.

    Also, if you provide DEFAULT value, then also duplicate entries aren't allowed in the primary key!

    So just by adding a new column in a non-empty table by giving default and declaring it primary key at the same time will not work.

    Now here comes AUTO_INCREMENT to rescue, add column by incrementing and declarig it as primary key:

    ALTER TABLE table_name ADD COLUMN new_column INT AUTO_INCREMENT PRIMARY KEY ;

    This works fine now...

    Thanks for asking.