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
    420
    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. #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,876
    Thanks
    3
    Thanked 455 Times in 445 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.
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  3. #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    420
    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. #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    420
    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
  •