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

[Xthemes.us]

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:

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

[Matis]

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

[RedHat]

Dit kun je toch afvangen op het moment dat je de insert doet? Hoeft helemaal niet op DB niveau.

[Xthemes.us]

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)

[Matis]

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

[Knutselsmurf]

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.

[ACM]

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]

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:

        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}

[Matis]

Xthemes.us schreef op zondag 7 oktober 2018 @ 22:00:
...

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.

[Xthemes.us]

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 .
(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]