Toon posts:

[MySQL] UNIQUE over 2 columns, beide kanten uit?

Pagina: 1
Acties:

Vraag


  • Xthemes.us
  • Registratie: Juli 2004
  • Laatst online: 11-11-2022
Mijn vraag
Ik heb een tabel waarin ik de relatie tussen 2 personen wil bijhouden, maar is er een manier om deze uniek te maken in beide richtingen.

Stel ik heb 2 personen "Harry" en "Kees"
SQL:
1
2
3
4
5
CREATE TABLE `friends` (
    `trainer` VARCHAR(255) NOT NULL,
    `trainer2` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`trainer`,`trainer2`)
);

Nu kan ik INSERT INTO FRIENDS VALUES('Harry', 'Kees'); uitvoeren, maar ook INSERT INTO FRIENDS VALUES('Kees', 'Harry');

Anders dan een trigger die de boel alfabetisch sorteert altevoren de insert in te voeren kan ik mij zo geen oplossing bedenken.

Relevante software en hardware die ik gebruik
MYSQL 10.1

MSI GX640 - 8GB RAM, Radeon 5970, 80GB SSD

Beste antwoord (via Xthemes.us op 07-10-2018 22:00)


  • ACM
  • Registratie: Januari 2000
  • Niet online

ACM

Software Architect

Werkt hier

Xthemes.us schreef op zondag 7 oktober 2018 @ 17:29:

Relevante software en hardware die ik gebruik
MYSQL 10.1
Die bestaat niet, je bedoeld vast de fork MariaDB 10.1

Voor regulier SQL is er de 'check constraint', waar je e.e.a. in af kan dwingen. MariaDb 10.2 ondersteund die blijkbaar, maar 10.1 dus nog niet: https://mariadb.com/kb/en...e/#constraint-expressions

Je zou dan in je constraint kunnen afdwingen dat id1 altijd kleiner moet zijn dan id2, waarna een unique constraint op id1+id2 verder afdwingt dat alle combinaties uniek zijn.
Knutselsmurf schreef op zondag 7 oktober 2018 @ 18:58:
Als je een query uit gaat voeren, dan heb je eenzelfde probleem. Je moet dan alle relatie-records dubbel controleren. Ik heb dit destijds opgelost door de relaties paarsgewijs toe te voegen. Je moet dan wel bijhouden welke relaties bij elkaar horen, zodat je ze ook paarsgewijs kunt verwijderen en bijwerken.
Dat zou je natuurlijk met een paar triggers kunnen regelen. Nadeel is dat eventuele inverse-opties dan wel op een vreemde manier errors kunnen geven als daar iets mis mee gaat.

Alle reacties


  • Matis
  • Registratie: Januari 2007
  • Nu online

Matis

Rubber Rocket

De vraag blijft: is de relatie Harry --> Kees hetzelfde als Kees --> Harry?
Ik kan me meer situaties van niet dan van wel eerlijk gezegd.

If money talks then I'm a mime
If time is money then I'm out of time


  • RedHat
  • Registratie: Augustus 2000
  • Laatst online: 28-01 11:56
Dit kun je toch afvangen op het moment dat je de insert doet? Hoeft helemaal niet op DB niveau.

  • Xthemes.us
  • Registratie: Juli 2004
  • Laatst online: 11-11-2022
Matis schreef op zondag 7 oktober 2018 @ 17:44:
De vraag blijft: is de relatie Harry --> Kees hetzelfde als Kees --> Harry?
Ik kan me meer situaties van niet dan van wel eerlijk gezegd.
In deze context is het eigenlijk precies wat de table name omschrijft, ze zijn simpelweg vrienden als één van de twee besluit dat ze ander niet meer als vriend beschouwen dat houdt de relatie op in beide richtingen.
RedHat schreef op zondag 7 oktober 2018 @ 17:47:
Dit kun je toch afvangen op het moment dat je de insert doet? Hoeft helemaal niet op DB niveau.
Dat kan maar ik heb geleerd dat je dergelijke restricties op het database niveau hoort af te handelen. Als je een andere frontend krijgt wil je alsnog dat dezelfde restricties gehandhaafd blijven toch? (hoe onwaarschijnlijk dit ook zal zijn)

MSI GX640 - 8GB RAM, Radeon 5970, 80GB SSD


  • Matis
  • Registratie: Januari 2007
  • Nu online

Matis

Rubber Rocket

Persoonlijk zal ik, alvorens de data in te voegen, sorteren op de twee namen (ids).

If money talks then I'm a mime
If time is money then I'm out of time


  • Knutselsmurf
  • Registratie: December 2000
  • Nu online

Knutselsmurf

LED's make things better

Als je een query uit gaat voeren, dan heb je eenzelfde probleem. Je moet dan alle relatie-records dubbel controleren. Ik heb dit destijds opgelost door de relaties paarsgewijs toe te voegen. Je moet dan wel bijhouden welke relaties bij elkaar horen, zodat je ze ook paarsgewijs kunt verwijderen en bijwerken.

- This line is intentionally left blank -


Acties:
  • Beste antwoord
  • +1Henk 'm!

  • ACM
  • Registratie: Januari 2000
  • Niet online

ACM

Software Architect

Werkt hier

Xthemes.us schreef op zondag 7 oktober 2018 @ 17:29:

Relevante software en hardware die ik gebruik
MYSQL 10.1
Die bestaat niet, je bedoeld vast de fork MariaDB 10.1

Voor regulier SQL is er de 'check constraint', waar je e.e.a. in af kan dwingen. MariaDb 10.2 ondersteund die blijkbaar, maar 10.1 dus nog niet: https://mariadb.com/kb/en...e/#constraint-expressions

Je zou dan in je constraint kunnen afdwingen dat id1 altijd kleiner moet zijn dan id2, waarna een unique constraint op id1+id2 verder afdwingt dat alle combinaties uniek zijn.
Knutselsmurf schreef op zondag 7 oktober 2018 @ 18:58:
Als je een query uit gaat voeren, dan heb je eenzelfde probleem. Je moet dan alle relatie-records dubbel controleren. Ik heb dit destijds opgelost door de relaties paarsgewijs toe te voegen. Je moet dan wel bijhouden welke relaties bij elkaar horen, zodat je ze ook paarsgewijs kunt verwijderen en bijwerken.
Dat zou je natuurlijk met een paar triggers kunnen regelen. Nadeel is dat eventuele inverse-opties dan wel op een vreemde manier errors kunnen geven als daar iets mis mee gaat.

  • Xthemes.us
  • Registratie: Juli 2004
  • Laatst online: 11-11-2022
ACM schreef op zondag 7 oktober 2018 @ 20:07:
[...]

Die bestaat niet, je bedoeld vast de fork MariaDB 10.1
Klopt als een bus, had MYSQL ingetypt hier op het forum en toen de browser tab naar phpmyadmin veranderd en simpelweg het nummer overgetypt. Het is inderdaad "10.1.35-MariaDB" (en lokaal: "10.2.15-MariaDB").
ACM schreef op zondag 7 oktober 2018 @ 20:07:
Voor regulier SQL is er de 'check constraint', waar je e.e.a. in af kan dwingen. MariaDb 10.2 ondersteund die blijkbaar, maar 10.1 dus nog niet: https://mariadb.com/kb/en...e/#constraint-expressions
Zeer interessant, wellicht tijd om de host een mailtje te sturen met het verzoek de database te updaten. Kan het in ieder geval lokaal alvast implementeren. Ik gebruik laravel/eloquent voor database beheer dus voorlopig los ik het als volgt op:
PHP:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
        Schema::create('eggfriends', function (Blueprint $table) {
            $table->string('trainer1');
            $table->string('trainer2');
            
            $table->foreign('trainer1')
            ->references('name')
            ->on('trainers');
            $table->foreign('trainer2')
            ->references('name')
            ->on('trainers');
        });       
        
        if (App::environment('local')) 
        {
            DB::unprepared('ALTER TABLE eggfriends
            ADD CONSTRAINT check_trainers_are_inserted_alphabetically 
            CHECK(trainer1 < trainer2)');
        }


Voor de meelezers die een andere database gebruiken kan een 'INSTEAD OF' trigger wellicht ook soelaas bieden. MYSQL/MariaDB ondersteunen die echter (nog) niet

MSI GX640 - 8GB RAM, Radeon 5970, 80GB SSD


  • Matis
  • Registratie: Januari 2007
  • Nu online

Matis

Rubber Rocket

Ik ken je database-opzet niet, dus daarom misschien een domme vraag: Waarom gebruik je de trainers naam in de relatie-tabel en niet het (bijvoorbeeld) id van de trainers?
Nu moet je, wanneer een trainer zijn naam wijzigt, (ik ga er gemakshalve van uit dat dit mogelijk is) ook de trainers naam in de relatie-tabel aanpassen. Mogelijk is de naam dan "groter" dan de naam van de tweede trainer waardoor je volgende check mank gaat.

If money talks then I'm a mime
If time is money then I'm out of time


  • Xthemes.us
  • Registratie: Juli 2004
  • Laatst online: 11-11-2022
Matis schreef op maandag 8 oktober 2018 @ 08:59:
[...]

Ik ken je database-opzet niet, dus daarom misschien een domme vraag: Waarom gebruik je de trainers naam in de relatie-tabel en niet het (bijvoorbeeld) id van de trainers?
Nu moet je, wanneer een trainer zijn naam wijzigt, (ik ga er gemakshalve van uit dat dit mogelijk is) ook de trainers naam in de relatie-tabel aanpassen. Mogelijk is de naam dan "groter" dan de naam van de tweede trainer waardoor je volgende check mank gaat.
Dat is inderdaad mogelijk, in dit geval zijn de trainernamen afkomstig uit een spel waarbij die uniek behoren te zijn (en ook als primary key in mijn trainer tabel gelden). En dat is een goed punt, dit blijkt een stuk lastiger te zijn dan ik dacht haha.

Niet ideaal, maar wellicht is de oplossing dan maar als INSERT INTO FRIENDS VALUES('Harry', 'Kees'); wordt uitgevoerd een trigger uit te voeren die zelf INSERT INTO FRIENDS VALUES('Kees', 'Harry') uitvoert. Dan ben ik er in ieder geval van verzekerd dat naamsveranderingen prima doorgevoerd worden en dat ik geen verdere dataverdubbeling krijg want daar heb ik zelf al voor gezorgd 8)7.
(en een tweede trigger die bij DELETE weer de omgekeerde waarden verwijderd). Wellicht is dit waar knutselsmurf op doelde.

[edit]
Ah.. MYSQL staat het niet toe de table te bewerken vanuit een trigger die wordt getriggered vanaf die tabel. Tjah dan maar geen SQL restricties en de boel vanuit de bovenliggende laag regelen. (PHP)

[Voor 7% gewijzigd door Xthemes.us op 08-10-2018 17:52]

MSI GX640 - 8GB RAM, Radeon 5970, 80GB SSD

Pagina: 1


Tweakers maakt gebruik van cookies

Tweakers plaatst functionele en analytische cookies voor het functioneren van de website en het verbeteren van de website-ervaring. Deze cookies zijn noodzakelijk. Om op Tweakers relevantere advertenties te tonen en om ingesloten content van derden te tonen (bijvoorbeeld video's), vragen we je toestemming. Via ingesloten content kunnen derde partijen diensten leveren en verbeteren, bezoekersstatistieken bijhouden, gepersonaliseerde content tonen, gerichte advertenties tonen en gebruikersprofielen opbouwen. Hiervoor worden apparaatgegevens, IP-adres, geolocatie en surfgedrag vastgelegd.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Toestemming beheren

Hieronder kun je per doeleinde of partij toestemming geven of intrekken. Meer informatie vind je in ons cookiebeleid.

Functioneel en analytisch

Deze cookies zijn noodzakelijk voor het functioneren van de website en het verbeteren van de website-ervaring. Klik op het informatie-icoon voor meer informatie. Meer details

janee

    Relevantere advertenties

    Dit beperkt het aantal keer dat dezelfde advertentie getoond wordt (frequency capping) en maakt het mogelijk om binnen Tweakers contextuele advertenties te tonen op basis van pagina's die je hebt bezocht. Meer details

    Tweakers genereert een willekeurige unieke code als identifier. Deze data wordt niet gedeeld met adverteerders of andere derde partijen en je kunt niet buiten Tweakers gevolgd worden. Indien je bent ingelogd, wordt deze identifier gekoppeld aan je account. Indien je niet bent ingelogd, wordt deze identifier gekoppeld aan je sessie die maximaal 4 maanden actief blijft. Je kunt deze toestemming te allen tijde intrekken.

    Ingesloten content van derden

    Deze cookies kunnen door derde partijen geplaatst worden via ingesloten content. Klik op het informatie-icoon voor meer informatie over de verwerkingsdoeleinden. Meer details

    janee