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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts

    compound key not preventing duplicates

    I need to add in new data to a table. This table had a unique key on just "plant_id". Because that key is just an auto-generated number, I've made it a compound key so I can do some inserting of new data, using these fields:

    plant_id, taxonomic_genus, scientific_name, infraspecific_epithet

    Done like this: TABLE tablename DROP UNIQUE KEY, ADD UNIQUE KEY (plant_id,taxonomic_genus,scientific_name,infraspecific_epithet)


    then when I tested by inserting what I know is a duplicate entry, It didn't prevent the duplicate. I did it again to be sure, and got a third entry.
    I'm stumped. This is exactly what every tutorial online says to do.

    My insert was this:
    INSERT IGNORE INTO tablename (taxonomic_phylum_divis, taxonomic_family, genus_hybrid_marker, taxonomic_genus, species_hybrid_marker, scientific_name, infraspecific_rank, infraspecific_epithet, author, taxonomic_domain, ipni, publication, publish_date) VALUES
    ('A','Acanthaceae','','Acanthopsis','','disperma','','','Nees','Unresolved','','','')
    Last edited by turpentyne; 01-11-2014 at 05:47 PM.

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,146
    Thanks
    2
    Thanked 333 Times in 325 Posts
    Because your unique key also contains the plant_id, which is apparently the incrementation column, the rows are different, the plant_id is different in each row.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts
    ahh.. ok

    at first I tried it without that, but it gave me an error when i made the key:

    Incorrect table definition; there can be only one auto column and it must be defined as a key

    This puts me in a trap.. I can't use 'plant_id' when I do this, but I still need it to auto increment. So is there something like... secondary key update ignore??
    Last edited by turpentyne; 01-11-2014 at 06:12 PM.

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts
    Hmmm.. I think I solved it by adding this:

    ALTER TABLE tablename ADD CONSTRAINT unique_names UNIQUE (taxonomic_genus,scientific_name,infraspecific_epithet)

    is this a correct thing to do? It seems to work


  •  

    Posting Permissions

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