4

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?

Improve this question

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Seems to be just what I need! Thanks!
If you want the query to fail gracefully, you can also add a further clause "ON DUPLICATE KEY UPDATE ID = ID;" on the end.
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
@Simonatmso.net yes, it all depends on the user :)
@Simonatmso.net What is the purpose of CONSTRAINT naming
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.