Süleyman Kenar Süleyman Kenar -4 years ago 159
MySQL Question

MySQL check 2 values already exists before insert

My columns are like this. column "a" is primary and auto incremantal.

a | b | x | y


When inserting new data, i need to check x and y columns shouldn't be exist together.

To clarify, imagine this row is at database with these values

(2, "example.com" , "admin", "123456")


I should able to insert both of these columns

(3, "example.com" , "user", "123456")
(4, "example2.com" , "admin", "123456")


But i shouldn't able to insert this column

(5, "example.com" , "admin", "5555555")


Because "example.com" and "admin" values already in database on a row. It doesn't matter column "y" is same or not.

How can i do this?

Answer Source

You want to let the database do the work. Although you can set up a condition within a query, that condition may not be universally true or someone might use another query.

The database can check this with a unique constraint or index. Actually, the unique constraint is implementing using a unique index:

create unique index unq_t_b_x on t(b, x);

(The columns can be in either order.)

The insert would then look like:

insert into t(b, x, y)
    values ('example.com', 'admin', '5555555')
    on duplicate key update b = values(b);

Note that the auto-incremented value is not included in the update.

The on duplicate key update just prevents the insert from generating an error. It is better than insert ignore because the latter will ignore all errors, and you just want to ignore the one caused by the duplicate key.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download