[SQL, Interbase] Uniek veld maar wel null waarden

[Verwijderd]

Een bepaald veld in een tabel wil ik als volgt hebben:

- alle waarden moeten unique zijn
- maar NULL waarden moeten wel mogelijk zijn


In Interbase mag je een veld niet declareren met UNIQUE zonder er NOT NULL bij te zetten. Dus UNIQUE kan ik er niet voor gebruiken.

Wat ik dan zou kunnen doen is eerst opvragen met een select of dat die waarde al bestaat, zo niet dan kan ik hem toevoegen.

Maar teneerste dan zou dat dus client-side gebeuren en volgens mij is het beter om dat server-side te doen, zodat je er zeker van bent dat er geen dubbele waarden voorkomen.

En als tweede kan het dus dat er meerdere clients allemaal tegelijkertijd dezelfde waarde willen toevoegen. Die zouden dan allemaal een select kunnen uitvoeren en allemaal krijgen ze een resultaat terug wat aangeeft dat de waarde nog niet aanwezig is.

Dus dit is ook geen oplossing.

Weet iemand een oplossing?

edit:

Sorry, vergeten topic titel af te maken.

Als iemand het even kan veranderen in:

[SQL, Interbase] Uniek veld maar wel null waarden

[elnino]

Op dinsdag 16 juli 2002 19:00 schreef tokkie het volgende:
Een bepaald veld in een tabel wil ik als volgt hebben:

- alle waarden moeten unique zijn
- maar NULL waarden moeten wel mogelijk zijn

Eigenlijk is het wel logisch dat deze twee dingen niet samen gaan. Als alles uniek moet zijn, kan er dus maar maximaal 1 rij met een waarde NULL hebben. Dat is dus de reden.

Maar ik weet niet zo snel een andere oplossing dan jij zelf opgeeft. Maar misschien zou je iets server-side kunnen maken (een soort van interface rondom de database heen), die zelf bijhoudt welke waarden er wel of niet gebruikt zijn. Deze methode is trouwens wel vrij omslachtig. Misschien weet iemand anders een betere oplossing, ik ben zelf niet echt een databasekenner.

Transactions zijn - volgens mij - ook niet echt een oplossing, aangezien het hier om een SELECT-query gaat. Maar misschien heeft iemand anders een betere oplossing.

[dusty]

titel aangepast.

[Delphi32]

Op dinsdag 16 juli 2002 19:00 schreef tokkie het volgende:
Een bepaald veld in een tabel wil ik als volgt hebben:

- alle waarden moeten unique zijn
- maar NULL waarden moeten wel mogelijk zijn

Zoals al eerder geschreven, je kan dan maar 1 record hebben waarin het veld een NULL waarde heeft, en dat wil je niet.

Je zou het kunnen oplossen door het uit te normaliseren: moet je dat veld ('t is vast Locatie of zoiets ) in een aparte tabel pleuren, en dan de FK vanuit je eerste tabel optioneel laten zijn.

Correctie: je moet in je nieuwe tabel een FK opnemen naar de eerste tabel, dan heb je een optionele 1:1 relatie. Of nog mooier, een koppeltabel ArtikelLocatie, waarin een combinatie Artikel en Locatie uniek is. (ik ga er dus maar even vanuit dat het om artikelen gaat die op een locatie moeten kunnen liggen)

[Verwijderd]

Geen enkele database laat NULL values toe in fields met een UNIQUE constraint. Dit is logisch want 2 rows met de waarde NULL zijn niet unique meer, dus een violation van de constraint, dus maakt de UNIQUE constraint de 'NULL value' functionaliteit obsolete.

Je zult dus moeten kiezen tussen NULL value support of de UNIQUE constraint. Kies je voor het laatste dan zul je bv dmv een bit field de geldigheid van het veld aan moeten geven, maar UNIQUE constraints op non-mandatory fields is hoogst ongebruikelijk, dus ik zou de uniqueness van het field op een andere manier proberen af te vangen. (bv in een trigger)

[Verwijderd]

Je zult dus een dynamische constraint moeten implementeren die nagaat of een bepaalde waarde zich al in de tabel bevind, zo niet dan mag de waarde toegevoegd worden. Ook mag de waarde toegevoegd worden als deze NULL is. Ik zie geen andere oplossing eerlijk gezegd.

[Verwijderd]

Op dinsdag 16 juli 2002 20:51 schreef Delphi32 het volgende:
Correctie: je moet in je nieuwe tabel een FK opnemen naar de eerste tabel, dan heb je een optionele 1:1 relatie. Of nog mooier, een koppeltabel ArtikelLocatie, waarin een combinatie Artikel en Locatie uniek is. (ik ga er dus maar even vanuit dat het om artikelen gaat die op een locatie moeten kunnen liggen)

Ik snap niet helemaal wat je hier bedoelt.

(Het gaat trouwens om de tabel: artikelen en het veld: artikelnummer. Het veld artikelnummer moet dus of een unieke waarde meekrijgen of leeg laten, dus null)

[Delphi32]

Op dinsdag 16 juli 2002 22:02 schreef tokkie het volgende:

[..]

Ik snap niet helemaal wat je hier bedoelt.

(Het gaat trouwens om de tabel: artikelen en het veld: artikelnummer. Het veld artikelnummer moet dus of een unieke waarde meekrijgen of leeg laten, dus null)

Hmm artikel en artikelnummer, en het artikelnummer mag leeg blijven? Ok het zij zo.

Suggesties hierboven, over het schrijven van een dynamische constraint, zijn met IB wel op te lossen middels een trigger. Die weg is dus ook zeker open, alleen zit je dan met het feit dat er iets server-side geregeld moet worden (nieuwe waarde van artikelnummer moet toegestaan worden, of geweigerd) wat uiteindelijk ook weer naar de client gecommuniceerd moet worden. Ok, is te doen.

En wat ik bedoelde is bij artikelnummers niet zo handig, bij locatie wel. Kijk zo:
Tabel Artikel(ArtikelID, en nog een hoop velden)
Tabel Locatie(LocatieID, Beschrijving ed)
Tabel ArtikelLocatie(ArtikelID, LocatieID)

Als je nu 'een artikel op een locatie plaatst', betekent dat een insert/edit in ArtikelLocatie tabel. Aangezien daarin ArtikelID en LocatieID samen de primary key vormen en dus uniek zijn, heb je dat stukje dicht. En simpel, zolang er voor een artikelID geen gerelateerd record in ArtikelLocatie bestaat, is de locatie onbekend en heb je dus eigenlijk jouw gewenste null-waarde. Vervang nu in dit verhaal elke occurrence van het woord 'locatie' door 'artikelnummer' en je verhaal is rond.

Alleen, ik vind artikelnummer teveel een directe eigenschap van artikel, dat mag eigenlijk niet in een losse tabel buiten de artikel-tabel. Dus kom je toch uit bij
Tabel Artikel(ArtikelID, Artikelnummer, en de rest)
en een trigger om Artikelnummer te valideren.

[Verwijderd]

Op dinsdag 16 juli 2002 22:02 schreef tokkie het volgende:
(Het gaat trouwens om de tabel: artikelen en het veld: artikelnummer. Het veld artikelnummer moet dus of een unieke waarde meekrijgen of leeg laten, dus null)

Artikelnr is toch het identificerende attribuut van de entiteit Artikel? Hoe kan dat nou NULL zijn!

/me doet verwoede pogingen zijn in straffe kramp getrokken kromme tenen weer recht te krijgen...

[Verwijderd]

Op woensdag 17 juli 2002 09:33 schreef Otis het volgende:
Artikelnr is toch het identificerende attribuut van de entiteit Artikel? Hoe kan dat nou NULL zijn!

Normaal gesproken zou artikelnummer een primare sleutel zijn. Maar ik wil dat wel alvast de artikel gegevens ingevuld kunnen worden als het artikelnummer nog niet bekend is (Het moet namelijk al uitgeprint kunnen worden.)

[Verwijderd]

Op woensdag 17 juli 2002 12:23 schreef tokkie het volgende:
[..]
Normaal gesproken zou artikelnummer een primare sleutel zijn. Maar ik wil dat wel alvast de artikel gegevens ingevuld kunnen worden als het artikelnummer nog niet bekend is (Het moet namelijk al uitgeprint kunnen worden.)

Wat voor waarde heeft dat, semantisch gezien, dan? Want je kunt er niets mee, tenminste als je in je organisatie niet 'artikelnr' als leidraad gebruikt ter identificatie van het artikel. Als jeiets anders gebruikt is 'artikelnr' overbodig.

[Verwijderd]

Uiteraard is het artikelnummer belangrijk. Hiermee worden tenslotte de artikelen besteld. Maar de informatie in de tabel artikelen wordt gebruikt om een bestelboek uit te kunnen printen.

Die bestelboeken worden om de 4 weken uitgeprint. Het komt dus voor dat een artikel al wel bekend is (naam, prijs, enz..) maar dat het artikelnummer nog niet bekend is.

Ik wil dus dat het wel wordt weergegeven in het bestelboek, alleen zal het artikelnummer en dus de barcode nog niet aanwezig zijn. (Maar die voeg je er later gewoon met de hand toe).

[Verwijderd]

Op woensdag 17 juli 2002 12:37 schreef tokkie het volgende:
Uiteraard is het artikelnummer belangrijk. Hiermee worden tenslotte de artikelen besteld. Maar de informatie in de tabel artikelen wordt gebruikt om een bestelboek uit te kunnen printen.

Die bestelboeken worden om de 4 weken uitgeprint. Het komt dus voor dat een artikel al wel bekend is (naam, prijs, enz..) maar dat het artikelnummer nog niet bekend is.

Ik wil dus dat het wel wordt weergegeven in het bestelboek, alleen zal het artikelnummer en dus de barcode nog niet aanwezig zijn. (Maar die voeg je er later gewoon met de hand toe).

In dat geval zou ik dat niet in de database proberen te tackelen, maar zoals al eerder gezegd met een stukje code.

In de trant van:

code:

If [Artikelnumnmer] IN (SELECT Artikelnummer FROM Producten;)

Then
MsgBox("Ey, dit artikelnummer is al in gebruik, kies een ander!")

Else
(UPDATE Producten SET Artikelnummer = [Artikelnummer])
End If

Zo even uit de losse pols...

[Verwijderd]

Dat losse pols stukje code kun je dan in een trigger onderbrengen die wordt geexecuteerd zodra je een row insert en draait in dezelfde transaction als het INSERT statement. Interbase ondersteunt als het goed is triggers.

[Verwijderd]

Ik ga dat van die trigger even proberen. Als het niet lukt, horen jullie het wel weer.

Alvast bedankt

[Verwijderd]

Ik heb het een en ander opgezocht over triggers en heb de volgende code gemaakt:

code:

CREATE EXCEPTION ARTIKELNUMMEREXISTS 'ARTIKELNUMMER ALREADY EXISTS';

SET TERM ^;

CREATE TRIGGER CHECKARTIKELNUMMER
FOR ARTIKELEN
ACTIVE BEFORE INSERT
AS
BEGIN
  if (NEW.ARTIKELNUMMER IN (SELECT ARTIKELNUMMER FROM ARTIKELEN WHERE ARTIKELNUMMER=:NEW.ARTIKELNUMMER;)) then
    EXCEPTION ARTIKELNUMMEREXISTS;
END ^

SET TERM ;^

Echter, het wil niet werken. Als ik deze code wil toevoegen m.b.v. IBConsole krijg ik een Dynamische SQL Error. Wat de error precies is en waar die ontstaat is niet terug te vinden.

Weet iemand wat er fout aan is?

[Delphi32]

Op vrijdag 19 juli 2002 18:51 schreef tokkie het volgende:
Ik heb het een en ander opgezocht over triggers en heb de volgende code gemaakt:

code:

CREATE EXCEPTION ARTIKELNUMMEREXISTS 'ARTIKELNUMMER ALREADY EXISTS';

SET TERM ^;

CREATE TRIGGER CHECKARTIKELNUMMER
FOR ARTIKELEN
ACTIVE BEFORE INSERT
AS
BEGIN
  if (NEW.ARTIKELNUMMER IN (SELECT ARTIKELNUMMER FROM ARTIKELEN WHERE ARTIKELNUMMER=:NEW.ARTIKELNUMMER;)) then
    EXCEPTION ARTIKELNUMMEREXISTS;
END ^

SET TERM ;^

Als ik het goed zie, zijn de namen voor je exception en je trigger te lang. Met een kortere exception naam komt ie er wel door, maar dan valt ie over je trigger.
Ben met je eens dat IB wel wat duidelijker foutmelding had mogen geven.

[The - DDD]

So de fuck zeg...

Waarom niet gewoon een extra tabel. Je probleem splitsen zeg maar.

Tabel oud:
|p-key|overige gegevens|Unieke waarde die ook meermaals null mag zijn|

Nieuwe tabellen:
|p-key|overige gegevens|

|ref-key naar p-key, deze kolom is echter ook primarykey van deze tabel|unieke waarde (UNIQUE constraint)|

Als er p-key niet voorkomt als ref-key dan is de unieke waarde die ook null mag zijn dus NULL. Een dubbele uniciteit op de tweede tabel is noodzakelijk, anders werkt dit niet. Lijkt me vrij duidelijk waarom.

[Verwijderd]

Ik ben hte met de poster hierboven eens: het opsplitsen van de tabel is beter. Immers: je systeem werkt rondom artikelnummers, oftewel artikelen die van een nummer zijn voorzien. Artikelen die nog niet een nummer hebben, zijn dus nog niet 'ingeklaard' oid, maw bevinden zich in een voorportaal om te worden toegelaten tot het systeem.

Een database is semantisch omgaan met je data, en imho is het opsplitsen van de tabel in 'artikelen met nummer' en 'artikelen zonder nummer die al wel zijn ingevoerd want dat is handig maar verder mag er niets mee want ze hebben geen nummer!' tabellen beter.