[MySQL] Afdwingen van maximaal bepaald value in rows

[WinstonC]

Hi!

Is het mogelijk om in een MySQL tabel af te dwingen dat er minimaal 0 maar maximaal 1 row een bepaalde value heeft in een column heeft opgeslagen?

Voorbeeld:
Er mag maar één actieve gebruiker zijn. Dit hoeft niet perse want er kunnen ook helemaal geen actieve gebruikers zijn. Hieronder

Het onderstaande zou dan bijvoorbeeld wel mogen:

SQL:

INSERT INTO users (name, active) 
VALUES ('piet', 1), ('henk', 0), ('jan', 0)

En dit ook:

SQL:

INSERT INTO users (name, active) 
VALUES ('piet', 0), ('henk', 0), ('jan', 0)

Maar dit niet omdat er twee actieve gebruikers zijn:

SQL:

INSERT INTO users (name, active) 
VALUES ('piet', 1), ('henk', 1), ('jan', 0)

Ik ben benieuwd naar jullie reacties!

[!GN!T!ON]

zet een unique restraint op de elementen die je uniek wilt hebben. MySQL geeft dan een error als je een duplicate value probeert op te slaan. Dit moet dan vervolgens in je code natuurlijk wel opvangen.

Andere optie is eerst een query uitvoeren om te kijken of er al een true staat in een van de records, en als dat niet het geval is dan je insert uit te voeren.

[ Voor 43% gewijzigd door !GN!T!ON op 20-02-2018 14:14 ]

[Rmg]

http://www.mysqltutorial.org/mysql-if-statement/

Je zou er een stored procedure voor kunnen gebruiken, als er al iemand actief is een error terug geven. of clearen en dan toch zetten. of iets anders.

[Ravefiend]

Je kan een BEFORE INSERT trigger overwegen.
23.3.1 Trigger Syntax and Examples

[Guillome]

Dit kan een unique index niet doen. En in dit geval is de usercase dusdanig dat je van te voren weet dat je een foutmelding kan verwachten.

Ofwel, doe eerst een select en kijk of je insert mag, en zo niet, handel dat netjes af naar de gebruiker.

Geen ingewikkelde poespas nodig met triggers of stored procs

[ Voor 13% gewijzigd door Guillome op 20-02-2018 14:26 ]

[Standeman]

!GN!T!ON schreef op dinsdag 20 februari 2018 @ 14:12:
zet een unique restraint op de elementen die je uniek wilt hebben. MySQL geeft dan een error als je een duplicate value probeert op te slaan. Dit moet dan vervolgens in je code natuurlijk wel opvangen.

Andere optie is eerst een query uitvoeren om te kijken of er al een true staat in een van de records, en als dat niet het geval is dan je insert uit te voeren.

Maar dan moet je wel met null values werken en niet met '0' voor zover ik weet.

Guillome schreef op dinsdag 20 februari 2018 @ 14:23:
Dit kan een unique index inderdaad doen. Maar in dit geval is de usercase dusdanig dat je van te voren weet dat je een foutmelding kan verwachten.

Ofwel, doe eerst een select en kijk of je insert mag, en zo niet, handel dat netjes af naar de gebruiker.

Geen ingewikkelde poespas nodig met triggers of stored procs

Maar wel oppassen met multi-threaded applicaties. De kans is misschien niet heel groot, maar dit kan wel leiden tot hele vage problemen wanneer twee threads dit tegelijkertijd willen doen. Dus een beetje poespas heb je wel nodig.

[ Voor 37% gewijzigd door Standeman op 20-02-2018 14:28 ]

[Rmg]

Unique indexes werken wel met null niet met 0.

[NMe]

Zelfs als MySQL dit eenvoudig zou ondersteunen hoort dit alsnog in de data-laag van je applicatie. Je zal immers ook een zinnige foutmelding moeten geven. Dus ik zou niet moeilijk doen met de stored procedures of triggers die hierboven genoemd worden (dat vergroot alleen maar het aantal stappen dat nodig is om je applicatie te installeren/migreren) en het gewoon netjes in de applicatie zelf checken.

Met een unique index kom je er zoals hierboven al gezegd is ook wel als je niet 0 en 1 maar NULL en 1 gebruikt, maar dat is ten eerste niet heel intuïtief en ten tweede moet het alsnog gecheckt worden in je applicatie zelf.

[Gomez12]

Standeman schreef op dinsdag 20 februari 2018 @ 14:25:
[...]
Maar wel oppassen met multi-threaded applicaties. De kans is misschien niet heel groot, maar dit kan wel leiden tot hele vage problemen wanneer twee threads dit tegelijkertijd willen doen. Dus een beetje poespas heb je wel nodig.

Waarom de focus op multi-threaded? In basis ga/kan je dit simpelweg al krijgen met een multi-user omgeving / meerdere client-instanties.

@TS is er een reden dat je een multi-insert doet? Want dat maakt het wel redelijk complexer (welke is bijv fout, is dat piet of henk)
Ik zou gewoon zeggen record voor record afhandelen. En dan zou ik zeggen :
- select eerst
- bepaal dan in je applicatie of je een update of een insert mag/wilt doen
- doe die update/insert

[borft]

@Gomez12 dat lost nog steeds concurrentie niet op (een ander proces kan een insert doen, na je select, maar voor je eigen insert). Ik zou zeggen probeer in een transactie een insert te doen en kijk dan hoeveel er active zijn. Als dat >1 is, dan een rollback. Let er wel op dat je isolatie level hiervoor hoog genoeg moet staan. En met MyIsam werkt het ook niet, je moet dan wel bv InnoDB gebruiken

[ Voor 11% gewijzigd door borft op 20-02-2018 16:02 ]

[kroegtijger]

NMe schreef op dinsdag 20 februari 2018 @ 14:59:
Zelfs als MySQL dit eenvoudig zou ondersteunen hoort dit alsnog in de data-laag van je applicatie. Je zal immers ook een zinnige foutmelding moeten geven. Dus ik zou niet moeilijk doen met de stored procedures of triggers die hierboven genoemd worden (dat vergroot alleen maar het aantal stappen dat nodig is om je applicatie te installeren/migreren) en het gewoon netjes in de applicatie zelf checken.

Met een unique index kom je er zoals hierboven al gezegd is ook wel als je niet 0 en 1 maar NULL en 1 gebruikt, maar dat is ten eerste niet heel intuïtief en ten tweede moet het alsnog gecheckt worden in je applicatie zelf.

Dit dus inderdaad. Een simpele if...then...else is genoeg hiervoor. Als je de mogelijkheid dus hebt zou ik het niet moeilijker maken dan strikt noodzakelijk. Alleen als je met heel erg veel traffic van doen zou hebben zou het misschien een probleem gaan geven, maar dan kijk je waarschijnlijk al naar heel andere oplossingen en methoden dan op MySQL op deze manier.

[Josk79]

Je kan ook de `active`-kolom verplaatsen naar een status-tabel met slechts één record met een kolom `active_user_id`

[kunnen]

Ik ben van de lelijke oplossingen, dus als je het per se in deze laag wilt doen:

SQL:

SELECT *, max(active) FROM users;

En dan zet je simpelweg de nieuwe active user op max(active)+1. De hoogste waarde is actief, alle lagere getallen zijn dat niet. Wel een groot genoeg int-type kiezen voor je column

[ Voor 12% gewijzigd door kunnen op 20-02-2018 19:30 ]

[Harrie_]

In het kader van lelijke oplossingen (@kunnen) zou je ook een tabel active_user kunnen maken met maar 1 column en 1 row; in dat veld zit dan de actieve user of deze is leeg.

_{Edit: ik zie dat @Josk79 deze optie al had genoemd}

[ Voor 17% gewijzigd door Harrie_ op 22-02-2018 09:24 ]

[Stemis]

Ravefiend schreef op dinsdag 20 februari 2018 @ 14:22:
Je kan een BEFORE INSERT trigger overwegen.
23.3.1 Trigger Syntax and Examples

Dit is voor deze use case de beste oplossing.

Echter voor netheid, is het erg lelijk om overal '0' op te slaan terwijl er maar 1 actief is.
Als je 100,000 users hebt, sla je dus 100.000 cijfers op terwijl je al weet dat er maar 1tje op 1 staat.

Daarom raad ik ook aan zoals de andere boven mij ookal hebben gezegd in een soort van config tabel de active user op te slaan:

code:

| slug            | value |
| active.user | 33      |

SELECT value FROM config WHERE slug = 'active_user'

Config tabellen zijn heel normaal en zie je ook terugkomen in veel applicaties.
Ik zelf gebruik ze ook

[ Voor 9% gewijzigd door Stemis op 21-02-2018 17:06 ]

[NMe]

Stemis schreef op woensdag 21 februari 2018 @ 17:05:
[...]

Dit is voor deze use case de beste oplossing.

Alleen als je het echt per se door je database wil laten regelen, en dat zou ik dus zoals gezegd niet doen buiten de normale constraints. Ik ben zelf redelijk tegen het gebruik van triggers in combinatie met een verder ook in jouw beheer zijnde applicatie omdat er dan ineens magic gebeurt als je in je applicatie iets doet die je niet kan plaatsen als je niet toevallig weet dat die trigger bestaat.

Daarom raad ik ook aan zoals de andere boven mij ookal hebben gezegd in een soort van config tabel de active user op te slaan:

Dat is inderdaad een prima oplossing. Al mijn webapplicaties die een stukje config hebben dat voor de gebruiker aanpasbaar is gebruiken dat inderdaad. Het hangt wel van de toekomstvisie van de TS af of dat nou wel of geen goed idee is: mocht het in de toekomst mogelijk moeten worden dat méér dan een gebruiker tegelijk actief kan zijn dan kun je maar beter de database daar klaar voor maken met de structuur uit de topicstart. Is dat ook in de toekomst überhaupt geen optie, dan zou ik voor jouw settings-suggestie kiezen.

[xzaz]

De vraag gaat over MySQL en dient dus daarop beantwoord te worden. Ja dit is mogelijk met BEFORE INSERT zoals eerder genoemd. Waarom de BL er opeens wordt bijgehaald is mij een raadsel.

[NMe]

xzaz schreef op donderdag 22 februari 2018 @ 13:32:
De vraag gaat over MySQL en dient dus daarop beantwoord te worden. Ja dit is mogelijk met BEFORE INSERT zoals eerder genoemd. Waarom de BL er opeens wordt bijgehaald is mij een raadsel.

We denken hier graag gewoon na over dingen. Als je een onhandige oplossing wil die perfect je vraag beantwoord maar die je eigenlijk niet moet willen dan moet je op StackOverflow zijn. Op Tweakers komen we met de goede/houdbare oplossing.

[xzaz]

NMe schreef op donderdag 22 februari 2018 @ 13:49:
[...]

We denken hier graag gewoon na over dingen. Als je een onhandige oplossing wil die perfect je vraag beantwoord maar die je eigenlijk niet moet willen dan moet je op StackOverflow zijn. Op Tweakers komen we met de goede/houdbare oplossing.

Als je het doet en als je stelt dat je 'graag nadenkt' over dingen moet je geen halve oplossing verkondigen. Dit oplossen in je BL is prima en daar ligt de verantwoordelijkheid ten dele maar dit gaat over de database integriteit en daarin is de genoemde oplossing prima.

Als je compleet wilt zijn zou TS zowel op zijn GUI / BL en Database dit moeten afvangen dus niet alleen op BL. Zoals eerder genoemd is een config tabel beter omdat de afgeleide van de relatie de 'active' aanduidt. UnitTests kunnen gemaakt worden op BL en op SQL niveau, Exception kan daar netjes worden afgevangen en bekeken als het resultaat gewenst is.

[NMe]

xzaz schreef op donderdag 22 februari 2018 @ 14:02:
[...]

Als je het doet en als je stelt dat je 'graag nadenkt' over dingen moet je geen halve oplossing verkondigen. Dit oplossen in je BL is prima en daar ligt de verantwoordelijkheid ten dele maar dit gaat over de database integriteit en daarin is de genoemde oplossing prima.

Database-integriteit? Nee hoor. Het interesseert die database geen bal of er meerdere users actief zijn, dit is gewoon een business rule. Unique constraints zijn in die zin fundamenteel anders omdat het ook indexes zijn.

Als je compleet wilt zijn zou TS zowel op zijn GUI / BL en Database dit moeten afvangen dus niet alleen op BL. Zoals eerder genoemd is een config tabel beter omdat de afgeleide van de relatie de 'active' aanduidt. UnitTests kunnen gemaakt worden op BL en op SQL niveau, Exception kan daar netjes worden afgevangen en bekeken als het resultaat gewenst is.

Een config-tabel vangt het net zo min op databaseniveau af, die zou je feitelijk zelfs op je filesystem (of beter: iets als redis) kwijt kunnen. Je dwingt daarmee, waar je hem ook laat, niks af en met de oplossing waarmee je dat wél doet heb je ineens business logic in je database staan, waar het niet hoort.

[WinstonC]

Bedankt voor jullie antwoorden! Ik had gehoopt dat er een kant en klare oplossing was in MySQL zoals bijvoorbeeld unique constaint. Dit blijkt niet zo te zijn waardoor ik ervoor heb gekozen om het op te lossen in mijn logics.

[CurlyMo]

NMe schreef op donderdag 22 februari 2018 @ 14:09:
[...]
Database-integriteit? Nee hoor. Het interesseert die database geen bal of er meerdere users actief zijn, dit is gewoon een business rule. Unique constraints zijn in die zin fundamenteel anders omdat het ook indexes zijn.

Dat is een beetje dezelfde discussie om naast je client side checks ook een dubbel check te doen aan de server side. Immer, je kan je javascript gewoon uitzetten of manipuleren.

Oftewel, als dit een harde fundamentele eis betreft van TS, dan is er niks mis mee om zowel in de database een constraint op te nemen als in je programmeertaal een check te doen. Normaal gesproken handelt je programmeertaal de checks af, maar als iemand daar een bug in maakt, dan heb je ook je database check nog. Inderdaad mis je op dat moment alleen de mooie foutmelding, maar behoud je wel consistentie.

[Standeman]

CurlyMo schreef op dinsdag 6 maart 2018 @ 15:16:
[...]

Dat is een beetje dezelfde discussie om naast je client side checks ook een dubbel check te doen aan de server side. Immer, je kan je javascript gewoon uitzetten of manipuleren.

Oftewel, als dit een harde fundamentele eis betreft van TS, dan is er niks mis mee om zowel in de database een constraint op te nemen als in je programmeertaal een check te doen. Normaal gesproken handelt je programmeertaal de checks af, maar als iemand daar een bug in maakt, dan heb je ook je database check nog. Inderdaad mis je op dat moment alleen de mooie foutmelding, maar behoud je wel consistentie.

Dus je gebruikt je database als automated testing tool zodat je slechte programmeurs kan afvangen

Database is bedoelt voor het opslaan van (relationele) data en niets anders. Alle andere zaken moeten gewoon op de juiste plek afgehandeld worden, anders gaat de boel enorm stinken.

En checks op de client -en of server is een non-discussie. Client side is UX only, server side BL only. Ze dienen twee compleet verschillende doelen.

[CurlyMo]

Standeman schreef op dinsdag 6 maart 2018 @ 15:39:
[...]
Dus je gebruikt je database als automated testing tool zodat je slechte programmeurs kan afvangen

Dat zeg ik niet. Ik vind alleen de stelligheid om het niet in de database te mogen doen onzin. Het kan prima óók in de database, alleen is de primaire plek om aan invoer validatie te doen je programmeertaal. Het is geen of-of.

[DJMaze]

en-en kan inderdaad, en ik gebruik het zodat ontwikkelaars niet per ongeluk iets vergeten (foute code kloppen).
Of eigenaren van de database zelf gaan klooien in de database.
Met name de tweede i.v.m. fiscale eisen in verschillende landen zodat men niet "per-ongeluk" omzet/facturen verwijdert. Daarmee dek ik dan af dat in iedergeval niet ik verantwoordelijk ben voor fouten in de administratie

SQL:

CREATE TRIGGER bu_users BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    DECLARE loggedin INT;
    SELECT COUNT(*) FROM users WHERE active > 0 INTO loggedin;
    IF 0 < loggedin THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Someone is already logged in';
    END IF;
END

[CurlyMo]

DJMaze schreef op woensdag 7 maart 2018 @ 11:02:
en-en kan inderdaad, en ik gebruik het zodat ontwikkelaars niet per ongeluk iets vergeten (foute code kloppen).
Of eigenaren van de database zelf gaan klooien in de database.

Precies, en daarom maak ik ook de vergelijking met frontend (UX) en backend. Het is juist gebruikersvriendelijk om in je frontend aan data-validatie te doen, alleen moet je er niet op vertrouwen. Het is gewoon fijn als een gebruiker clientside een foutmelding krijgt als de gebruiker een verkeerde NL postcode invoert of als wachtwoorden niet hetzelfde zijn bij het aanmaken. Vervolgens check je dat opnieuw serverside. En je kan het zelfs nog op database niveau checken middels constraints (bijv. in geval van een postcode).

Het gaat er in deze gevallen simpelweg om wat het belang van zo'n bedrijfsvoeringsregels is. Als je een Nederlandse gemeente bent, dan is de kern van je gegevensverzameling een valide BSN. Is er geen valide BSN, dan is de persoon niet meer uniek te identificeren. Dat kan je dus prima afdwingen in je database.

Laatst gesproken met iemand die adressen administratie doet bij een overheidsinstelling. Er zijn in Nederland duidelijke normen voor een geldige adres notatie, maar je moest eens weten wat voor een zooi er allemaal in zo'n registratie terecht komt. Simpelweg, omdat de applicatiebouwers de normen onvoldoende laten afdwingen in hun gebouwde applicatie, wat vaak komt door vage specs vanuit een opdrachtgever die de normen ook niet of nauwelijks kent.

[DJMaze]

CurlyMo schreef op woensdag 7 maart 2018 @ 11:13:
Simpelweg, omdat de applicatiebouwers de normen laten afdwingen in hun gebouwde applicatie, wat vaak komt door vage specs vanuit een opdrachtgever die de normen ook niet of onvoldoende kent.

Dit dus. Maar ook met correcte specs gaat het fout, kan niet eens fatsoenlijk een '+' gebruiken in e-mailadressen. Bij ziggo wordt dat door de "applicatie" in de database opgeslagen met een "spatie" (urldecode() application/x-www-form-urlencoded )

[ Voor 4% gewijzigd door DJMaze op 07-03-2018 16:21 ]

[CurlyMo]

DJMaze schreef op woensdag 7 maart 2018 @ 16:20:
[...]

Dit dus. Maar ook met correcte specs gaat het fout, kan niet eens fatsoenlijk een '+' gebruiken in e-mailadressen. Bij ziggo wordt dat door de "applicatie" in de database opgeslagen met een "spatie" (urldecode() application/x-www-form-urlencoded )

Ik heb mijn zin aangepast, omdat het er verkeerd stond Er moest dus onvoldoende tussen.