Handling Duplicate Data with INSERT IGNORE
A multi-table relational schema organizes data across separate tables to eliminate redundancy and improve data integrity. The Artist, Album, and Track tables in this example demonstrate how to structure a music library database.
A Repeated CSV Row
A CSV file can place all information about one track on a single row: the track title, artist name, album title, track length, rating, and play count. That format is convenient for reading, but it repeats artist and album information whenever the same artist or album appears on another row. The relational design solves this by separating the information into Artist, Album, and Track tables. The key question is what should happen when a CSV row names an artist or album that the database already contains.
What do you think happens?
Suppose the Artist table already contains an artist name and the application attempts to insert that same name again. What should happen when the insert uses INSERT IGNORE?
Reveal answer
Answer: The existing row is silently preserved and the duplicate insert is skipped.
The UNIQUE constraint identifies the duplicate value. INSERT IGNORE then handles the duplicate by skipping the insert rather than raising an error.
Three Tables, Three Responsibilities
The schema contains three tables, each responsible for one kind of entity. Artist stores unique artist names. Album stores album titles and the artist_id of the artist who created each album. Track stores individual songs, along with metadata such as duration, rating, and play count, and it uses album_id to identify the related album. Separating these responsibilities minimizes redundancy because an artist or album does not need to be copied into every track record.
The relationships form a chain. An album points to an artist through artist_id, and a track points to an album through album_id. To find the artist of a track, first follow the track's album_id to the Album table, then follow that album's artist_id to the Artist table.
Uniqueness Before Insertion
The UNIQUE keyword enforces a uniqueness constraint on a column. In this schema, Artist.name is marked TEXT UNIQUE, and the title column is also given a UNIQUE constraint in Album and Track. A duplicate value in one of these constrained columns is rejected by the database. With INSERT IGNORE, that rejected duplicate insert is silently skipped instead of raising an error.
| Table | Important value | Purpose |
|---|---|---|
| Artist | name | Prevents two Artist rows from having the same artist name |
| Album | title | Prevents duplicate album-title values under the stated schema |
| Track | title | Prevents duplicate track-title values under the stated schema |
From CSV Row to Related Records
Loading one track record
A CSV row contains a track title, artist name, album title, track length, rating, and play count. Trace how the application places this information into the normalized schema.
Artist lookup: The application first checks whether the artist already exists in Artist. If the artist is not present, it inserts the artist and retrieves the new id. If the artist is already present, the UNIQUE constraint identifies the duplicate and INSERT IGNORE skips the duplicate insert.
Album lookup: The application then checks whether the album exists, using the artist_id obtained from the Artist step. A new album is inserted if needed; an existing album is protected from a duplicate insert by the UNIQUE constraint and INSERT IGNORE.
Track insertion: Finally, the application inserts the track with its title, track metadata, and the album_id that identifies its album.
Relationship chain: The completed track record reaches the artist indirectly: Track.album_id identifies the album, and Album.artist_id identifies the artist.
The flat CSV row becomes one or more related records without repeatedly storing the artist and album information.
The order matters because later records need identifiers produced or retrieved earlier. The album step needs artist_id, and the track step needs album_id. The application therefore does not try to insert the track independently of its related records.
Removing Repeated Information
Normalization organizes data to minimize redundancy and improve data integrity. In the music-library design, artist information is stored in Artist, album information is stored in Album, and song information is stored in Track. References connect those records instead of copying the artist name and album details into every track row. If an artist releases a new album, the design adds one Album row and multiple Track rows without creating another Artist row.
Treat UNIQUE constraints and foreign keys as database-level protection, not merely as checks performed by application code. UNIQUE constraints help prevent duplicate artists or albums, while foreign keys preserve the links between related records. Together they help maintain the integrity of the relational model.
Mistakes in the Loading Process
Storing the artist name directly in Album instead of using artist_id
The schema uses artist_id as a reference to a row in Artist. Storing the name directly would not follow the described foreign-key relationship.
Fix:
Store the id value from Artist in Album.artist_id and follow that reference when the artist name is needed.Inserting the track before identifying its album
The Track table links each song to an album through album_id.
Fix:
Find or insert the artist first, find or insert the album second, and insert the track third.Assuming INSERT IGNORE creates another copy
The UNIQUE constraint identifies the duplicate, and INSERT IGNORE silently skips the duplicate insert.
Fix:
Reuse the existing artist or album record and continue with the relevant relationship id.Relying only on application checks
The source design uses constraints as an additional protection for relational integrity.
Fix:
Use UNIQUE constraints together with foreign keys and INSERT IGNORE behavior.
Practice the Sequence
A CSV row contains a track title, an artist name, an album title, track length, rating, and play count. Describe the order in which the application should work with Artist, Album, and Track. Then explain what happens if the artist already exists and what happens if the album already exists.
Hints
- Begin with the artist name and determine whether Artist already contains it.
- Use the artist_id obtained from Artist when checking or creating the Album record.
- Use the album_id obtained from Album when creating the Track record.
- Apply the UNIQUE and INSERT IGNORE behavior when a value is already present.
Checking a practice answer
Explain the complete loading order for one CSV row.
First: Find or insert the artist and obtain artist_id. If the artist is already present, INSERT IGNORE skips the duplicate insert.
Second: Find or insert the album using artist_id and obtain album_id. If the album is already present, its duplicate insert is skipped.
Third: Insert the track, including its metadata and album_id.
The row is represented across the three normalized tables, with references connecting the records and UNIQUE constraints protecting against duplicate values.
Key Takeaways
- Artist, Album, and Track separate different kinds of information so artist and album details are not repeatedly stored in track records. Album uses artist_id to reference Artist, while Track uses album_id to reference Album. UNIQUE constraints prevent duplicate values in the designated columns. INSERT IGNORE silently skips an insert when a UNIQUE constraint detects a duplicate. A CSV row is loaded in stages: artist first, album second, and track third.
Key Takeaways
- Artist, Album, and Track divide music-library data into separate normalized tables.
- Foreign keys connect Album to Artist through artist_id and Track to Album through album_id.
- UNIQUE constraints prevent duplicate values in the designated artist, album, and track columns.
- INSERT IGNORE silently skips a duplicate insert instead of raising an error.
- CSV loading proceeds from artist to album to track so each later table can use the required relationship id.