[Answered ]-Adding a new Unique Field in an existing database table with existing values- Django1.7/MySql

by

2👍

✅

I will show the following with the new column being an INT not a CHAR. Same difference. 

create table t1
(   id int auto_increment primary key,
    col1 varchar(100) not null
);

insert t1(col1) values ('fish'),('apple'),('frog');
alter table t1 add column col2 int; -- OK (all of col2 is now NULL)
ALTER TABLE t1 ADD UNIQUE (col2); -- OK (now a UNIQUE constraint on col2)

show create table t1;
CREATE TABLE `t1` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `col1` varchar(100) NOT NULL,
   `col2` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `col2` (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Now let’s start over and see it blow up.

drop table t1;
create table t1
(   id int auto_increment primary key,
    col1 varchar(100) not null
);
insert t1(col1) values ('fish'),('apple'),('frog');
alter table t1 add column col2 int not null; -- OK (at the moment)
-- note: (all of col2 is now 0)
ALTER TABLE t1 ADD UNIQUE (col2); -- error 1062 duplicate entry '0' for key 'col2'

The reason the above blew up was because the NOT NULL on the col2 add column made all that data a 0. Then the UNIQUE constraint attempt failed.

Now below let’s continue the thought:

drop table t1;
create table t1
(   id int auto_increment primary key,
    col1 varchar(100) not null
);
insert t1(col1) values ('fish'),('apple'),('frog');
alter table t1 add column col2 int; -- OK (all of col2 is now NULL)
ALTER TABLE t1 ADD UNIQUE (col2); -- OK
select * from t1; -- col2 is NULL for all 3 rows
update t1 set col2=7 where id=1; -- OK
update t1 set col2=7 where id=2; -- error 1062 duplicate entry '7' for key 'col2'

The moral of the story is that if you add a column to a table with data pre-existing, and want that new column to be unique, you need to have it nullable to start. Then create the unique constraint. Now all data is NULL so the unique constraint is forgiving. But once you tweak the data to be non-NULL, it better be unique. Tweak meaning UPDATE or INSERT. So col2 needs to remain NULL or UNIQUE thereafter.

👤Drew

Leave a comment