This is really basic, but it drove me crazy. Suppose you want to add a row of data to a MySQL database, but not if there’s already an entry in it with the essence of that data. For example, suppose you have a table of notes and the titles they’re clustered under, and don’t want to allow the same note to appear more than once for any particular title. Assume also that that table is also recording an identifier of which book the note is about.

Here’s a SQL statement that works (line breaks don’t matter):

INSERT IGNORE into 
playlistentries(title,noteid,bookid) 
VALUES('fiction', '1234','5678');

With the exception of the mysterious “IGNORE” (see below), this is a straightforward command that inserts a row into the table “playlistentries” with a series of values (‘fiction’, ‘1234’,’5678′) mapped to the series of fields (title, noteid, bookid). If there’s already a row with those title and noteid values, the table will be left unchanged. Otherwise, a new row will be added.

But, this will not work unless you set up your MySQL table so that it has a unique key based on the fields you’re testing for (title and noteid in this example). That way when you go to insert a row that has an already-existing title and noteid, it will be automatically rejected. The “IGNORE” in the SQL statement means it will be rejected without creating an error message that just gets in the way.

To set up your table so that it has the right unique key, use a SQL statement like this:

ALTER TABLE playlistentries 
ADD UNIQUE KEY noteidtitle (noteid,title(100))

This tells MySQL to create a unique key (named “noteidtitle”) based on the fields noteid and title. The “(100)” is there to tell MySQL that it should only look at the first 100 characters of the title field; if you’ve set up your table with that field as “text,” you’ll get an error unless you put a limit on it. A hundred characters is probably 75 more than I need.

Note also that you only run the “alter table” command once in the lifetime of your database.

Finally, please note that there is a high probability that what I’m telling you is wildly inefficient, non-robust, and suboptimal. On a scale of 1-100, I am about 3 points past raw beginner. But these commands work for me, and I am assuming as always that if you’re reading this, you are an amateur like me engaged in some small project designed primarily for your own use. Improvements and do-overs will be gladly accepted.

Categories: tech dw