CODEWITHSUNDEEP
mysqlsqlddl
RLe
RLe

Reputation: 486

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

Upvotes: 2

Views: 5383

Answers (3)

Kartik Gautam
Kartik Gautam

Reputation: 128

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.

Upvotes: 2

Sai Harini Perugupalli
Sai Harini Perugupalli

Reputation: 1

Your column might have null values If your table doesn't have a primary key and would like to add a new column and make it as a primary key, use the below query and use auto increment so it will be unique

ALTER TABLE old_table ADD pk_column INT AUTO_INCREMENT PRIMARY KEY;

Upvotes: 0

umair qayyum
umair qayyum

Reputation: 286

Your column might have Null values in it, and also try dropping the primary key constraint first if there is any.

try this DDL:

ALTER TABLE table_name ADD ( column_name VARCHAR (10) SET DEFAULT 'value1');
ALTER TABLE table_name ADD PRIMARY KEY(column_name);

Upvotes: 1

Related Questions