MySQL INSERT query to avoid inserts

Question

I have a MySQL table as follows:

+----+--------+---------+
| id | userid | otherid |
+----+--------+---------+
|  1 |      1 |       2 |
|  2 |      1 |       3 |
|  3 |      3 |       5 |
+----+--------+---------+

I don't want the pairing of userid and otherid to reappear in the table either way round. So for example the following inserts should not appear in the table:

INSERT INTO users (userid, otherid) VALUE ('2', '1');
INSERT INTO users (userid, otherid) VALUE ('1', '3');
INSERT INTO users (userid, otherid) VALUE ('3', '5');

What do I include in the INSERT to stop these inserts? Or do I have to do a separate SELECT query first? I know you can pair unique columns, but this won't reject the INSERTS above.

mamdouh alramadan · Accepted Answer · 2013-12-01 00:49:55Z

2

if I understood your question, then what you are looking for is UNIQUE CONSTRAINT.

you can add it like this:

ALTER TABLE table_name ADD UNIQUE(user_id,other_id);

in this case if an insert attempted to rewrite a condition where user_id and other_id exists mysql will throw an error.

Comments

Shaw Levin · Accepted Answer · 2013-12-01 00:46:39Z

0

You could do another select in the function doing the insert or you could set up a trigger depending on what you want to happen. Do you want the insert to fail? An error to go back to user?

Triggers allow you to perform another call after something like an insert. http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-triggers/

If the data is coming from a user you might want to also consider checking the data before it gets sent to the database. Validating it on the client side would save some time by reducing invalid calls.

answered Dec 1, 2013 at 0:46

Shaw Levin

1951 gold badge1 silver badge9 bronze badges

Comments

user3053392 · Accepted Answer · 2013-12-01 11:06:03Z

0

But that'll only stop:

INSERT INTO users (userid, otherid) VALUES ('1', '2');

I also what it to reject

INSERT INTO users (userid, otherid) VALUES ('2', '1');

Looking for something like

IF (userid = '1' AND otherid = '2') OR (userid = '2' AND otherid = '1') then fail otherwise input (userid = '1' AND otherid = '2')

Sorry not sure best way to explain

answered Dec 1, 2013 at 11:06

user3053392

113 bronze badges

Comments

Joshua Carnifexia · Accepted Answer · 2015-02-22 17:25:41Z

0

Also, for performance reasons, you really don't want to refer to integer values as string literals if you have them defined as integers in the database.

It causes an unnecessary type conversion internally in MySQL which will become apparent at larger scale workloads.

answered Feb 22, 2015 at 17:25

Joshua Carnifexia

214 bronze badges

Collectives™ on Stack Overflow

MySQL INSERT query to avoid inserts

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related