2

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
)
  • Please post the 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) – a_horse_with_no_name Aug 6 '15 at 14:59
  • why not just check in you code if something exists in the DB, do nothing if it is already there and add it if it is missing. Then look for everything in the db which is not in your list. – Julien Vavasseur Aug 6 '15 at 15:05
  • What Julien suggests is a valid option. Another is your idea: delete all the rows with same FK, then insert all the news ones, both (DELETE and INSERT 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"). – ypercubeᵀᴹ Aug 6 '15 at 15:07
  • @a_horse_with_no_name check the updated question – user72255 Aug 6 '15 at 15:17
  • @JulienVavasseur that is an option, though it would make my code longer and messier so I was looking if there is something more elegant. – user72255 Aug 6 '15 at 15:18
0

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy