Preventing MySQL double insert

Question

I have a table more or less looking like

Name | Lastname | ID | Date

Is there a way to prevent the database from running the insert function if a person which such Name, Lastname and ID already exists without running additional queries searching for him?

score 13 · Accepted Answer · answered Apr 02 '13 at 12:44

13

add a UNIQUE constraint on the columns,

ALTER TABLE TableName ADD CONSTRAINT tb_uq UNIQUE (ID, LastName)

once it has been implemented, if you try to insert a value which ID and LastName already existed, it will throw an exception. example

INSERT INTO tableName (ID, LASTNAME) VALUES (1, 'hello') // ok
INSERT INTO tableName (ID, LASTNAME) VALUES (2, 'hello') // ok
INSERT INTO tableName (ID, LASTNAME) VALUES (1, 'hello') // failed

answered Apr 02 '13 at 12:44

John Woo

258,903
69
498
492

Seems to be just what I need! Thanks! – user2228063 Apr 02 '13 at 12:48
2

If you want the query to fail gracefully, you can also add a further clause "ON DUPLICATE KEY UPDATE ID = ID;" on the end. – Purpletoucan Apr 02 '13 at 12:49
I'd be inclined to leave it to fail ungracefully so that you can handle it within the application, but then again all depends on the users intentions – Simon at The Access Group Apr 02 '13 at 12:53
@Simonatmso.net yes, it all depends on the user `:)` – John Woo Apr 02 '13 at 12:54
@JW out of interest any particular reason for CONSTRAINT instead of a UNIQUE KEY? Have always used UNIQUE KEY myself but if there's a performance reason it could be good to knoe for future reference. Unless the two are considered identical of course :) – Simon at The Access Group Apr 02 '13 at 13:00
1

@Simonatmso.net [What is the purpose of CONSTRAINT naming](http://stackoverflow.com/questions/1397440/what-is-the-purpose-of-constraint-naming) – John Woo Apr 02 '13 at 13:05

Preventing MySQL double insert

1 Answers1