Assignment Table INSERT INTO prevent duplicates

Question

I'd like to create a statement for my PHP function where I have an UserID and an InteressenID and update my userinteressen table depending on what the user has checked. I don't want duplicates in that table.

I have found the INSERT ... ON DUPLICATE KEY UPDATE in documentation, however, that doesn't work since in the table doesn't have an UNIQUE row set. Due working with MySQL, I don't have the MERGE statement. Since I receive the InteressenID from my PHP function, there's no need for a JOIN. I tried INSERT IGNORE described here, but it returned Error #1064 For deleting, I was thinking about first setting the InteressenID to 0 and delete them afterwards.

The tables:

User

ID | Name
---+--------
1  | "Agathe"
2  | "Bernd"
3  | "Charlie"

Interessen

ID | Name
---+------------
1  | "Cars"
2  | "Sport"
3  | "Languages"

userinteressen

User | Interessen
-----+-----------
1    | 2
1    | 3
2    | 2

My not working attempts:

INSERT INTO userinteressen VALUES('$id','$interesse')
SELECT *
WHERE userinteressen.User IS NULL AND userinteressen.Interesse IS NULL;

INSERT IGNORE INTO `userinteressen`
SET `User`= 1
`Interesse`= 2;

More Informations: I'm using PDO. The userinteressen-table was created by using the interface of phpmyadmin, thus I had no real idea, what the DB looked like.

I exported the database to get some insights. I'm new to databases and had mostly theory. Here are the statements created by the Engine:

--
-- Indices for Tabelle `userinteressen`
--
ALTER TABLE `userinteressen`
  ADD KEY `User` (`User`,`Interesse`),
  ADD KEY `Interesse` (`Interesse`);

--
-- Constraints of table `userinteressen`
--
ALTER TABLE `userinteressen`
  ADD CONSTRAINT `userinteressen_ibfk_1` FOREIGN KEY (`User`) REFERENCES `user` (`UserID`),
  ADD CONSTRAINT `userinteressen_ibfk_2` FOREIGN KEY (`Interesse`) REFERENCES `interessen` (`InteressenID`);

COMMIT;

Thanks!

I'm confused. Given that we don't want duplicates in that table, why wouldn't we enforce a unique constraint, by created a unique index on the combination of the two columns? With a unique constraint, we can use `INSERT IGNORE` and `INSERT ... ON DUPLICATE KEY` e.g. `ALTER TABLE userinteressen ADD UNIQUE KEY userinteressen_UX1 (user, interessen)`. (Alternatively, we could define the PRIMARY KEY to be a composite key, those same two columns. Likely we are also going to need an index with `interessen` as the leading column. (InnoDB will automatically create indexes to support foreign keys.) — spencer7593, Jan 02 '19 at 15:54
The reason why I didn't enforce a unique constraint is I wasn't aware of this possibility. I'm new to databases (only had it for one year and we started with MS Access). That's a helpful information, thank you! — Schattennarr, Jan 03 '19 at 00:58
More informations about "[How to update a junction table?](https://giustino.blog/how-to-update-a-junction-table/)" — Steffen Mächtel, Jan 03 '19 at 15:18

Steffen Mächtel · Accepted Answer · 2019-01-03T15:02:03.403

mysql REPLACE:

Is User and Interesse in your junction table userinteressen a composite PRIMARY KEY? Then you can use mysql REPLACE instead of INSERT:

REPLACE INTO `userinteressen`
SET `User`= 1
`Interesse`= 2;

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.6, “INSERT Syntax”.

But there is no solution for unchecked "interesse". So you need something to delete relations in userinteressen, when a user unchecks one or more.

Example to delete:

DELETE FROM `userinteressen` WHERE `Interesse` NOT IN (1,2,3) AND `User` = 1;

Replace (1,2,3) with the selected interesse id´s

Delete all relations before inserting:

You can also delete all relations (tuples) and insert the current selected again. This also allows for user to uncheck a "interesse" and its deleted:

// ...
DELETE FROM `userinteressen` WHERE `User` = 1
// ...
foreach($selectedInteressen as $id) {
    // ...
    INSERT INTO `userinteressen`
    SET `User`= 1
    `Interesse`= $id;
    // ...
}

NOTICE: I dont know which interface you are using (PDO, mysqli, ...?) so i simplified the example

I'm using PDO as interface. I'm not sure what you mean with combined PRIMARY KEY though. `ALTER TABLE `userinteressen` ADD KEY `User` (`User`,`Interesse`), ADD KEY `Interesse` (`Interesse`); I'm updating my post for more information. — Schattennarr, Jan 03 '19 at 01:11
Oh its called composite PRIMARY KEY and not combined PRIMARY KEY. I updated my answer. Its a PRIMARY KEY over more then one field. `PRIMARY KEY (User, Interesse)`. [How can I define a composite primary key in SQL?](https://stackoverflow.com/questions/1110349/how-can-i-define-a-composite-primary-key-in-sql) — Steffen Mächtel, Jan 03 '19 at 15:07

Assignment Table INSERT INTO prevent duplicates

1 Answers1