Naveen Kumar V Naveen Kumar V - 7 months ago 15
SQL Question

MySQL Unique Key for more columns not working

I am trying to make Names unique for the authors. But if I try,

create table author(author_id int auto_increment primary key, firstname varchar(20), middlename varchar(20), lastname varchar(20)) auto_increment = 1001;
alter table author add unique(firstname, middlename, lastname);


Example 1:

insert into author(firstname, middlename, lastname) values('Alice',null,null);
insert into author(firstname, middlename, lastname) values('Alice',null,null);

select * from author;

1001 | Alice | NULL | NULL
1002 | Alice | NULL | NULL


If I give all three name parts, it works fine.

Example 2:

insert into author(firstname,middlename,lastname) values('Alice','Bob','Charlie');
insert into author(firstname,middlename,lastname) values('Alice','Bob','Charlie');
>> Error...Duplicate


Why names with null is not considered?

How to make it unique even for Example 1?

Answer

Instead of NULL, use empty string(''), so that your DML becomes,

insert into author(firstname, middlename, lastname) values('Alice','','');
insert into author(firstname, middlename, lastname) values('Alice','','');
>> Error: Duplicate Entry

Having null is unknown to MySql, for that reason we need this way. Thanks if anyone took effort but couldn't answer.