How to Resolve 'UNIQUE constraint failed: sqlite3 result code 1555' Errors in SQLite?

How to Handle UNIQUE Constraint Failed Error in SQLite

Issue:

When inserting data into your SQLite database, you receive the error "UNIQUE constraint failed: sqlite3 result code 1555."

Explanation:

This error occurs when you attempt to insert data with a primary key value that already exists in the table. By default, SQLite enforces unique constraints on primary key columns to prevent duplicate entries.

Solution:

There are two primary solutions to handle this error:

1. Ignore Duplicate Records:

To ignore duplicate records and continue inserting other data, use the following syntax:

INSERT OR IGNORE INTO (column1, column2, ...) VALUES (value1, value2, ...);

2. Replace Existing Records:

To replace the existing record with the new data, use the following syntax:

INSERT OR REPLACE INTO (column1, column2, ...) VALUES (value1, value2, ...);

Example:

In your provided code, you are inserting duplicate list_id values in the list table and duplicate item_id values in the item table. To handle this error, you can use the following modified code:

Modified list Insert Statements:

INSERT OR IGNORE INTO list VALUES (1, "user1-list1", 1);
INSERT OR IGNORE INTO list VALUES (2, "user1-list2", 1);
INSERT OR IGNORE INTO list VALUES (3, "user1-list3", 1);
INSERT OR IGNORE INTO list VALUES (1, "user2-list1", 2);
INSERT OR IGNORE INTO list VALUES (1, "user3-list1", 3);
INSERT OR IGNORE INTO list VALUES (2, "user3-list2", 3);

Modified item Insert Statements:

INSERT OR IGNORE INTO item VALUES (1, "user1-list1-item1", "FALSE", 1);
INSERT OR IGNORE INTO item VALUES (2, "user1-list1-item2", "FALSE", 1);
INSERT OR IGNORE INTO item VALUES (1, "user1-list2-item1", "FALSE", 2);
INSERT OR IGNORE INTO item VALUES (1, "user1-list3-item1", "FALSE", 3);
INSERT OR IGNORE INTO item VALUES (2, "user1-list3-item2", "FALSE", 3);
INSERT OR IGNORE INTO item VALUES (1, "user2-list1-item1", "FALSE", 1);
INSERT OR IGNORE INTO item VALUES (2, "user2-list1-item1", "FALSE", 1);
INSERT OR IGNORE INTO item VALUES (1, "user3-list1-item1", "FALSE", 1);
INSERT OR IGNORE INTO item VALUES (1, "user3-list3-item1", "FALSE", 2);

By using either the INSERT OR IGNORE or INSERT OR REPLACE syntax, you can resolve the UNIQUE constraint failed error and insert your data as intended.

The above is the detailed content of How to Resolve 'UNIQUE constraint failed: sqlite3 result code 1555' Errors in SQLite?. For more information, please follow other related articles on the PHP Chinese website!