-1

I have a problem when I try to import data

create table song (
    artist_id int not null,
    album_id int not null,
    song_name varchar(75),
    tracknum int not null,
    primary key (artist_id, album_id, tracknum),
    foreign key (artist_id) references artist(artist_id)
        on delete restrict
        on update cascade,
    foreign key (album_id) references album(album_id)
        on delete restrict
        on update cascade
    );

How do I fix the duplicate value? I am trying to upload a data dump from files. It is working for all the tables except this one.

  • the error was due to the primary key and columns being out of order seems to work when I fixed that – Caylan Kowalski Oct 12 at 21:59
  • This is not clear. Please clarify via edits, not comments. If you solved this, please consider posting an answer. For code questions please give a minimal reproducible example. How to Ask help center – philipxy Oct 13 at 7:29
1
  1. Edit your structure, remove both PRIMARY KEY definition and FOREIGN KEYS definitions. Or, if the table is now empty, simply drop it and re-create without PK and FKs.

  2. Import your data.

  3. Execute

    ALTER TABLE song ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
    
  4. Execute

    SELECT song.*
    FROM song 
    NATURAL JOIN ( SELECT artist_id, album_id, tracknum
                   FROM song
                   GROUP BY artist_id, album_id, tracknum
                   HAVING COUNT(*) > 1 ) dups
    ORDER BY artist_id, album_id, tracknum;
    

    You will see ALL duplicates.

    Check id values of excess row and remove them:

    DELETE FROM song
    WHERE id IN ({duplicates ids list})
    

    Of course if you find 2 duplicated rows you must delete only one of them.

  5. Remove id column

    ALTER TABLE song DROP COLUMN id;
    
  6. Restore primary key and foreign keys

    ALTER TABLE song ADD primary key (artist_id, album_id, tracknum);
    ALTER TABLE song ADD  foreign key (artist_id) references artist(artist_id)
            on delete restrict
            on update cascade;
    ALTER TABLE song ADD foreign key (album_id) references album(album_id)
            on delete restrict
            on update cascade;
    

    If some FK creation fails gather the values which have no matching values in reference table and create them then repeat FK creation.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.