Search code examples
mysql

Mysql: how to create a unique key for every column?

I have created this table

CREATE TABLE mytable (
    ID bigint NOT NULL AUTO_INCREMENT,
    Caller varchar(255) NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (ID));

ALTER TABLE `mytable` ADD UNIQUE `unique_index`(`Name`, `Caller`);

the problem:

If I insert

insert into mytable values ('value1','12929393');

and then

insert into mytable values ('value1','12929393');

block it beacause duplicate (and is ok)

but If I insert

insert into mytable values ('value1','12929344');

or

insert into mytable values ('value2','12929393');

It accept and I prefer block it because value 12929393 is duplicate, is possible to do this in mysql or mariadb?

Solution

  • Just add unique indexes on each column separately, not a composite index.

    ALTER TABLE `mytable` ADD UNIQUE `unique_index_1` (`Name`);
ALTER TABLE `mytable` ADD UNIQUE `unique_index_2` (`Caller`);