mysql - Able to enter multiple values even when new value equals an existing primary key

Question

I have a table defined as follows:

CREATE TABLE  `mydb`.`users` (
  `userID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `createdDate` datetime NOT NULL,
  `active` tinyint(1) NOT NULL,
  `lastUpdatedDate` datetime NOT NULL,
  PRIMARY KEY (`userID`,`userName`) USING BTREE
)

However, when I run this query:

REPLACE INTO users SET userName ='Joe', active=1, lastUpdatedDate=now()

it inserts multiple rows with a userName Joe even though that is a primary key. I think because both userID and userName are primary keys, the key is only when both of them match. However, I can't remove the primary key on the userID as mysql throws an error. I want the userName to be distinct so there is only one record for each userName, and I want the replace statement to just update the lastUpdatedDate if the userName already exists (or insert it if it doesn't exist). How can I accomplish this?

Answer 1

You are correct userID and userName together make up the primary key. Your replace into automatically creates the userID value thus creating valid unique keys

(1,joe)
(2,joe)
....

Issue this

ALTER TABLE `mydb`.`users` ADD CONSTRAINT UNIQUE uniqueusername (`userName`);

Which adds the wanted constraint on your table. Additionally to (userID,userName) being the primary key now the userName column by itself can only contain unique values.

You can use this query and get the wanted behaviour (it updates lastUpdatedDate and active when the userName exists and else inserts a new entry with lastUpdatedDate and createdDate set to now()).

INSERT INTO `users` (`userName`, `createdDate`, `active`, `lastUpdatedDate`)
VALUES ('Joe', now(), 1, now())
ON DUPLICATE KEY UPDATE `lastUpdatedDate` = now(), active = 1;

Answer 2

You answered your own question, I think -- userName isn't "a" primary key, userName is part of the primary key. It seems like you want something like:

CREATE TABLE  `mydb`.`users` (
`userID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`createdDate` datetime NOT NULL,
`active` tinyint(1) NOT NULL,
`lastUpdatedDate` datetime NOT NULL,
PRIMARY KEY (`userID`) USING BTREE
UNIQUE KEY (`userName`)
)

(not syntax checked)

Answer 3

First, check your table for duplicate id's;

select userName
from users
group by userName
having count(*) > 1

Remove rows until there are no duplicates. Then, remove the existing primary key:

alter table users drop primary key

And recreate the primary key you'd like:

alter table users add primary key (userName)

This should now work, since there are no longer multiple rows with the same username.

Answer 4

CREATE TABLE  `mydb`.`users` (
  `userID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `createdDate` datetime NOT NULL,
  `active` tinyint(1) NOT NULL,
  `lastUpdatedDate` datetime NOT NULL,
  PRIMARY KEY (`userId`) USING BTREE,
  UNIQUE KEY (`userName`)
)

Something to be aware of is that REPLACE will actually remove the row and add a new one. This will result in a new userID (due to the AUTO_INCREMENT)