Mysql – Insert record only if a combination of column values doesn’t exist

MySQL

I have a simple MYSQL insert query:

INSERT INTO table('col1','col2','col3') VALUES('123','456','some text')

I want to insert only if the above combination of columns does not already exist and this case is only when col1 has value of 123 and col3 has values of some text

I will try to explain with some examples of what is allowed and what not:

('123','456','some text') allowed ONLY if not exists

('123','847,'some text') allowed ONLY if not exists

Both results above can coexists together because col2 is different. However, each of the above results can exist only once

The following ones can be duplicate because they don't have the combination of col1 and col3 of the values 123 and some text respectively

('412','847','some text') allowed no matter what even if exists

('763','372','any text') allowed no matter what even if exists

To summarize, a combination of col1,col2,col3 should be unique ONLY when col1 has value 123 and col3 has value some text

The primary key of the table is an auto_increment id.

Can this be achieved by setting certain indexes on the table or only through subqueries or what are the solutions available here to achieve this?

Best Answer

MYSQL doesn't support such types of constraints. You should use stored procedure for inserting data. So you can do some checking and validation data.

Related Solutions

MySQL query ‘going away’ on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob

This sounds like you have to increase the size of your MySQL Packets

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

Since MySQL Packets can hold rows of data, larger items in the packet can cause a lot of packets to filter in-and-out to prevent whole chunks of related data from splitting during processing. This can be a silent killer of DB connections for no apparent reason. If fact, I wrote a post about how this can affect certain mysqldumps.

Try increasing the max_allowed_packet (256M) using the following command:

SET max_allowed_packet = 1024 * 1024 * 256;

Mysql – What would be the fastest approach to bulk update this thesql table (Innodb)

The first thing you need to determine about col2 is if it can be a PRIMARY KEY.

Run this query

SELECT COUNT(1),col2 FROM table GROUP BY col2 HAVING COUNT(1) > 1;

If nothing comes back, then col2 can be a UNIQUE KEY. If even one row comes back, then col2 cannot be a UNIQUE KEY. You can create an index on it.

Since this query would take a while without an index, just go ahead and index it

ALTER TABLE table ADD INDEX col2 (col2);

but that might take a very long time.

You may need a temp table to accomplish this:

Step 01) Create a table that will hold the one million col2 values

DROP TABLE IF EXISTS col2values;
CREATE TABLE col2values ENGINE=InnoDB SELECT col2 FROM table WHERE 1=2;
ALTER TABLE col2value ADD PRIMARY KEY (col2);

Step 02) You can load the col2values table with the one million col2 values

Step 03) Index col2

CREATE TABLE table2 LIKE table;
ALTER TABLE table2 ADD INDEX col2 (col2);
INSERT INTO table2 SELECT * FROM table;
DROP TABLE table;
ALTER TABLE table2 RENAME table;

Step 04) Perform update using INNER JOIN

UPDATE col2values A
INNER JOIN table B
USING (col2)
SET B.col1=1;

In the above steps

Step 03 needs to be done only once
Step 03 is done with 5 lines rather than ALTER TABLE table ADD INDEX col2 (col2); because it would require a full rollback operation should anything go wrong.
All other steps are your bulk insert steps going forward

Give it a Try !!!

Best Answer

Related Solutions

MySQL query ‘going away’ on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob

Mysql – What would be the fastest approach to bulk update this thesql table (Innodb)

Related Question