1

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

RLe
  • 456
  • 12
  • 28
  • Please tag with the database you are using. Your code could only work if the table were empty, because any existing row would get a `NULL` value for `column_name`, and that would violate the primary key constraint. – Gordon Linoff May 22 '19 at 18:30
  • Create a different table and insert there all your data. Delete old table. Rename new table. No data lost. – PM 77-1 May 22 '19 at 18:31
  • @GordonLinoff hi im using mysql and i edited my question :( and im not allowed to empty the table – RLe May 22 '19 at 18:40

3 Answers3

2

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.

Kartik Gautam
  • 108
  • 7
  • :( oh i found out it makes my existing primary key into multiple key – RLe May 22 '19 at 20:37
  • I added pic of how my table look like – RLe May 22 '19 at 21:15
  • Firstly DROP PRIMARY KEYS, Then We've to divide the task into 3 steps: Firstly, add the column using auto_increment, secondly change the new column to varchar removing auto_increment then thirdly drop the primary keys and then finally add primary keys with columns you want.---> ALTER TABLE table_name DROP PRIMARY KEY; ---> ALTER TABLE table_name ADD COLUMN newData6 INT AUTO_INCREMENT PRIMARY KEY ; ---> ALTER TABLE table_name CHANGE newData6 newData6 VARCHAR(50); ----> ALTER TABLE table_name DROP PRIMARY KEY;---->ALTER TABLE table_name ADD PRIMARY KEY(Data3, Data4, newData6); – Kartik Gautam May 23 '19 at 19:40
1

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);
umair qayyum
  • 286
  • 3
  • 11
0

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;

Sai Harini Perugupalli
  • 1