I have a table with three columns, ID, a foreign key field and a text field (unique). The user should be able to update all text fields where the FK has a certain value.
The problem is that the user can enter as many values and in whatever order he likes. If he enters less than there are in the database all of them should be removed and replaced with the new ones, for example:
The names are already in the database: John, Amy and Joe.
The user wants to update these names with the following: Amy and Jessica. Which leads to an error (because he is trying to update the value John with Amy when Amy already exists) and a problem (if it would work): the value Joe would not be deleted.
Because of this I see myself forced to have two statements: DELETE and then INSERT. The problem with this is that if one of the insert statements fails, I will lose the data that was in the database initially (John, Amy and Joe).
I've tried setting up a transaction to solve the problem so that I could rollback if something failed. However, this leads me back to the original problem: Integrity constraint violation: 1062 Duplicate entry 'John' for key 'Name'.
The names correspond to items whose characteristics are stored in a separate table, since each item can have various names.
What does my database design need to look like for this problem not to occur?
I've asked this question on StackOverflow already, and it was suggested I should revisit my database design. Here is the original question (with code): https://stackoverflow.com/questions/31727353/update-a-unique-column-with-an-unknown-number-of-terms
CREATE TABLE Items
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
Characteristics varchar(255)
)
CREATE TABLE Names
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
ID_ITEM int,
Name varchar(255) UNIQUE
)
create table
statements for your tables and some sample data and what you want to do with that sample data (edit your question, don't post additional information as comments)DELETE
andINSERT
statements) in one transaction. Did you try that? This is more in line with your requirements ("The problem is that the user can enter as many values and in whatever order he likes. If he enters less than there are in the database all of them should be removed and replaced with the new ones").