[Resolved] [Resolved] INSERT (but don't duplicate)

aaron_karp

I think this deals with the properties of my table, but I don't know that much about databases so I thought I'd ask.

Basically I want to insert a row into a table, but only if an identical one doesn't already exist.

The table consists of an autoinc primary key ID and two foreign keys.

Any suggestions of how to do this other than to first run a SELECT then, if it returns 0 rows, run the INSERT? I'm sure there's a better way. Do I make the table rows unique somehow?

Thanks!

Roger_Ramjet

Guessing ** this is a matrix that links 2 tables (2 foriegn keys) to normalise a many to many relationship. Yes??

it does not need it's own autoincrement field.
create a unique index using the 2 foriegn key fields, this will be the primary key
use REPLACE instead of INSERT, it inserts if not present and overwrites if present, so you get no error http://dev.mysql.com/doc/mysql/en/REPLACE.html

aaron_karp

Thanks Roger. That looks like it will do the trick 🙂

aaron_karp

So, just to clarify -

I'm going to drop the column that's currently the autoinc pk

What should the settings be on the two foreign keys? Unique, Foreign key?

Here's how the database is set up:

Table1            Table2
     \            /
       \         /
        \Table3/ (Table 3 has the two foreign keys)
              |
              |
           Table 4 (Refers to the PK of Table3)

Pretty picture, huh?

Basically the point of Table 3 is to prevent data redundancy, since Table 3 also holds some things that apply to many things in Table 4, like Date and Author.

If I remove the PK from Table 3, I won't be able to refer to it anymore, will I?

......hope this makes sense......

Roger_Ramjet

Sorry, aaron. When I suggested that you change the indexing of table 3, it was on the assumption that table 3 was just a matrix to resolve a many-many relationship. Not in your case since it holds author, etc: so don't loose the PK on table 3.

Look, it is difficult to analyse someone elses table structure unless they post the table layouts, and explain the uses that they intend for the data. Otherwise I'm just pissing in the wind. I'm sure you understand your pic but it could mean anything to me. At the very least you MUST indicate the type of relationship between tables, one-one, one-many, many-many. Better if your detail all fields and their indexes.

aaron_karp

You're right. Upon further reflection that picture is pretty vague.

I think I need to reevaluate what I'm trying to do. The REPLACE option was really what I needed to know.

If I still run into any trouble, I'll come back with some more specifics. Thanks again.

-Aaron

Stressed

I'm not completely understanding what you are looking for but there are different options.

One thing you can do if you have the right primary keys setup is an INSERT IGNORE INTO .....

As long as the primary keys are the same, it will ignore the 2nd insert. This however will not update any information, just drop the insert if the keys match.

If you are running the 4.x version of mysql you could do something like. INSERT INTO table (a,b,c) VALUES('1','2','3') ON DUPLICATE KEY UPDATE c=3

What this does is if it finds a duplicate key it updates c=3. This is good if you are like wanting to increment account, and this saves you from having to do 2 queries (First a select then an insert).

HTH
Andy

aaron_karp

Ok got it figured out. Thanks guys.