Mysql – Update a unique column with an unknown number of values

database-designMySQL

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
)

Best Answer

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

suggest that a reasonable option would be your idea:

  • DELETE all the rows with same FK,
  • then INSERT all the news ones,

both (DELETE and INSERT statements) in one transaction. I tried in SQLdiddle and it worked just fine:

CREATE TABLE Names
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
ID_ITEM int,
Name varchar(255) UNIQUE
) ;

INSERT INTO Names
  (ID_ITEM, Name)
VALUES
  (1, 'John'),
  (1, 'Amy'),
  (1, 'Joe') ;


START TRANSACTION ;
    DELETE FROM Names
    WHERE ID_ITEM = 1 ;

    INSERT INTO Names
      (ID_ITEM, Name)
    VALUES
      (1, 'John'),
      (1, 'Joe') ;
COMMIT ;

Then:

SELECT * FROM Names ;

returns:

ID   ID_ITEM   Name
--------------------
4     1        John
5     1        Joe
Related Question