2010
05.19

For a project I was working on I needed a multi field (column) key. Both `id` and `server` were not unique, but put together they were. I already had `id` as my PRIMARY KEY, but that was not correct anymore. So the first thing to do was remove that KEY:

ALTER TABLE `clients` DROP PRIMARY KEY

And after that I created the `server` field:

ALTER TABLE `clients` ADD `server` VARCHAR(20) NOT NULL AFTER `uid` ;

According to this post there a few things to think about:

  1. a multiple PRIMARY KEY can’t use AUTO_INCREMENT
  2. each multiple field KEY is only allowed to spread 16 columns
  3. there is a maximum overall length of 256 Bytes

After considering this (and concluding there were no problems) I created the PRIMARY KEY:

ALTER TABLE `clients` ADD PRIMARY KEY (`uid`, `server`) ;

If we wanted to have an UNIQUE KEY we should executed:

ALTER TABLE `clients` ADD UNIQUE (`uid`, `server`) ;

6 comments so far

Add Your Comment
  1. Arpan Das

    Hi,
    I have two Unique keys.
    But with duplicate data at insertion, the second unique key is not giving me error…

    // table structure
    CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(255) NOT NULL,
    `password` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`,`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    // insertion part
    $sql = “INSERT INTO `users` (`id`, `username`, `password`, `email`)
    VALUES (NULL, ‘test’, ‘123’, ‘test@test.com’)”;

    what I want?
    1. error on duplicate username
    2. error on duplicate email

    any idea?

  2. tersmitten

    The above schema gives you an error when inserting / updating an username / email combination that is not unique. I think this is what you want:

    CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(255) NOT NULL,
    `password` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`),
    UNIQUE KEY `email` (`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

  3. Arpan Das

    Thanks a lot tersmitten

    Its working great 🙂

    Yes, it is the exact thing what I wanted.

  4. Arpan Das

    But I dont know why phpMyAdmin gave me that sql.
    I made username and email, both unique at the time of table creation in phpMyAdmin.

  5. kk

    WOW!, solved my issue, thanks a ton!

  6. […] ter Smitten’s » MySQL; How to make a PRIMARY- or UNIQUE key of multiple fields […]