Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Unique index across multiple fields

    I recently came across this question:
    http://stackoverflow.com/questions/6...lumns-in-mysql

    I just want to make sure I am understanding the answer correctly. (table names and fields have been made generic to protect the innocent)

    If I were to issue the command:
    Code:
    alter table tablename add unique index(field2, field3);
    I could then alter my insert statement to include ON DUPLICATE KEY.
    From my searching about the ON DUPLICATE KEY clause, I'm finding a lot of options and I'm not quite sure how I would do this. To put it simply, I'm inserting data across 4 fields (field1,field2, field3, field4). I specify the unique index across field2 and field3 so when I insert, if the data being inserted is the same as an existing record on the field2 and field3 columns, I want nothing to be done (ie, don't insert a new record and don't update an existing record).

    It looks like my insert command would have to look something like this (note that I insert records via a php form):
    PHP Code:
    "INSERT INTO tablename (field1, field2, field3, field4) VALUES ('$_POST[field1]','$_POST[field2]','$_POST[field3]','$_POST[field4]') ON DUPLICATE KEY DO NOTHING"
    Can anyone verify my understanding of this, or perhaps point out flaws in my thinking.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Simpler than that:

    INSERT IGNORE ...

    That's all you need. Don't need the ON DUPLICATE KEY stuff if you just want to ignore the duplication error.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    mharrison (03-04-2013)

  • #3
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Simpler than that:

    INSERT IGNORE ...

    That's all you need. Don't need the ON DUPLICATE KEY stuff if you just want to ignore the duplication error.

    Once again, thank you. Followup question. Is there a way to have the query let me know which records it did not insert due to being duplicates. Not really important, just more curiosity on my part.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Yes. When an insert succeeds, the number of rows affected is 1. When it doesn't that number is 0.

    http://php.net/manual/en/function.my...ected-rows.php
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Just had a thought...if I am adding a unique index to a column...am I going to run into problems if the data in that column is identical....for example...I could have multiple entries where the value is "Happy"....I don't care about it being unique in that column, I care about the uniqueness of a new record between the two columns when looked at together. Not sure if I am explaining that correctly...makes sense in my head but then again, I am running on 3 hours of sleep.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Try it. You'll be pleasantly surprised. Or maybe not surprised,but still happy.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Dec 2012
    Posts
    54
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Try it. You'll be pleasantly surprised. Or maybe not surprised,but still happy.
    Well, I was surprised...not in the happy way.

    Ended up getting: #1071 - Specified key was too long; max key length is 1000 bytes.

    In looking at my records, there is no way I can modify them at all in order to generate a unique index from them.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Ahhh...bummer. You know, keys *can* be built from partial fields. Could you guaranteed a unique key in that way?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    738
    Thanks
    20
    Thanked 85 Times in 85 Posts
    cant you just add a column that is auto increment?
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Quote Originally Posted by Arcticwarrio View Post
    cant you just add a column that is auto increment?
    How will that help him detect that the 4 fields he NEEDS to be unique actually are unique? All that would do is ensure that he doesn't duplicate the auto increment column. Kind of useless for his purposes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •