2010
05.19
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:
- a multiple PRIMARY KEY can’t use AUTO_INCREMENT
- each multiple field KEY is only allowed to spread 16 columns
- 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`) ;
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?
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 ;
Thanks a lot tersmitten
Its working great 🙂
Yes, it is the exact thing what I wanted.
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.
WOW!, solved my issue, thanks a ton!
[…] ter Smitten’s » MySQL; How to make a PRIMARY- or UNIQUE key of multiple fields […]