Hej,

Jag har ett fält i en dialog som användaren INTE är tvungen att skriva något i, men om användaren skriver något så måste innehållet vara unikt i den tabellen det sparas. Ett exempel : I en Customer-tabell vill jag spara organisationsnummer som ska vara unikt för varje företag i Sverige, men det är inte säkert att användaren vet kundens organisationsnummer så fältet ska få lämnas tomt.

Första tanken är givetvis ett Unique-constraint eller index, men eftersom fältet inte måste fyllas i så kommer det att finnas många Null-värden (eller tomsträngar eller vad man nu väljer för värde för tomma fält). Men det verkar inte fungera eftersom databasen bara tillåter EN post som är Null o s v.

Givetvis kan man ställa en fråga mot databasen först för att se om värdet är unikt, men vi har 4-5 unika fält i detta formulär så det känns surt att behöva ställa 4-5 extra frågor varje gång...finns det någon genväg?

/Per Hultqvist

Jag skulle nog använda mig av en trigger för att kontrollera att värdena är unika. Om dom inte är det kan man signalera fel (RAISERROR i SQL Server) och göra en Rollback.

Vilken databas använder du dig av?

Ooops det glömde jag : SQL-server 2000

Angående triggers-lösningen : Tack, det verkar som en bra lösning...men visst vore det bra om det fanns en inställning på Unique-constraint/index att den ska ignorera Null:s? :-)

/Per Hultqvist

Det har ingenting med inställningar att göra, det är rent logiskt. Om du har två okända värden finns det en möjlighet att de är samma värde, alltså kan man inte tillåta flera.

Det finns väl ingenting som hindrar en databastillverkare att tillverka en variant på ett Unique-constraint som ignorerar Null-värden och letar efter dubletter bland icke-null-värden? En sådan "feature" skulle iallafall jag vilja ha...

/Per Hultqvist

Null är inget värde. Det är avsaknad av värde. Det tyckerr jag är en stark fördel med access att man kan ha en unik index som kan innehålla flera nullvärden. Men det skulle vara en fördel om det var valbart om den skulle inkludera nullvärden i Indexet eller inte.

Unique acdepterar väl null bara man inte har kolumnen som primary key eller har den med i ett 'clustered' index?

/johan/


Jag testade lite, en rad med null går bra. Trigger är nog bästa lösningen.

Nja, det finns egentligen två typer av NULL (relationsmodellens skapare Dr E F Codd förespråkar faktiskt att man ska ha två olika typer av nulls, och därmed ha fyrvärdeslogik istället för den trevärdeslogik som SQL har), Unknown och Not applicable. Dvs, man kan ha NULL i en kolumn därför att man inte vet värdet av någon anledning, eller så har man det därför att det inte passar för den aktuella entiteten (detta kan ofta innebära att man har en dålig databasdesign).

Chris Date, en av de övriga skaparna av relationsmodellen och en stor personlighet inom databasvärlden förespråkar snarare att NULLs inte alls ska finnas. Han skriver bl a i "An introduction to database systems" (en av de bästa böckerna man kan läsa om databasdesign):

"Our recommendation to DBMS users would thus be to ignore the vendor's three-valued logic support entirely, and to use a disciplined 'default values' scheme instead (thereby staying firmly in two-valued logic)."

ANSI och ISO har dock valt att i SQL-standarden gå emot bägge dessa linjer, och istället ta en mellanväl, med ett värde (NULL) för både UNKNOWN och NOT APPLICABLE. Vidare säger man "NULL is never equal to NULL", men däremot "NULL duplicates NULL". Det är anledningen till att flera NULLs hamnar i en gemensam grupp i en fråga med GROUP BY. Det var också varför jag här ovan hävdade att en unique-constraint (observera att det handlar inte om index, att SQL Server använder ett unique index för att se till att en unique constraint efterlevs är en DBMS-specifik implementation) endast tillåter en rad med NULL i kolumnen som UNIQUE kontrollerar. Efter lite djupare efterforskningar verkar det dock som att SQL-standarden säger att UNIQUE-constraints _ska_ tillåta multipla NULLs, men så är allstå inte fallet i SQL Server.

Det här har fått mig ganska intresserad av detta, så jag kommer att utforska vidare och återkommer med definitivt svar...

Tack för ett svar som visade hur lite jag egentligen vet om databaser på djupet :-)

Först en följdfråga till ditt svar. Jag förstår skillnaden som påpekas mellan de olika NULL-värdena, och att detta kan ses som två olika värden. Den tredje typen av värden som jag kan tänka mig är de normala värden som representeras av datatypen (alltså heltalen om vi talar om datatypen Int), men vilket är det fjärde "elementet" i fyrvärdeslogiken?

Nästa fråga. Förstod jag det rätt att dom rekommenderar att använda t ex -1 för okända värden (eller icke applicerbara) hellre än att sätta dom som NULL?

Fråga tre : Du skrev "NULL is never equal to NULL". Borde inte det innebära att UNIQUE-constrainten borde fungera precis så som jag vill att dom ska fungera i mitt fall? Det verkar ju snarare som att reglen är "NULL is equal to NULL".

Hoppas du har tid att utveckla ditt svar ytterligare, för det här var mycket intressant...

Slutligen, höll på att glömma, hur anser du att jag borde lösa mitt ursprungliga problem? Alltså att ett fält i en tabell måste vara unikt OM man angett ett värde i kolumnen, dock är det ok att lämna ett eller flera fält tomma.

/Per Hultqvist

...
> vilket är det fjärde "elementet" i fyrvärdeslogiken?

Nja, du har missuppfattat det lite. SQL har idag trevärdeslogik, TRUE, FALSE och UNKNOWN. Vad E F Codd menar med fyrvärdeslogik är TRUE, FALSE, UNKNOWN och NOT APPLICABLE. Detta härstammar bl a från en gammal ISO-standard på hur man ska ange en persons kön (jag kan ha fel på platsen för UNKNOWN och N/A, möjligen ska de skiftas):

0: Unknown
1: Male
2: Female
9: Not applicable

> Förstod jag det rätt att dom rekommenderar att använda t ex -1 för okända värden (eller icke applicerbara) hellre än att sätta dom som NULL?

Nja, egentligen inte. De hävdar att SQL-standarden är fel, att NULLs i den form de existerar är helt enkelt inte användbart.

> Fråga tre : Du skrev "NULL is never equal to NULL". Borde inte det innebära att UNIQUE-constrainten borde fungera precis så som jag vill att dom ska fungera i mitt fall? Det verkar ju snarare som att reglen är "NULL is equal to NULL".

Japp, håller på att diskutera detta på annan plats. Verkar dock inte som att det finns så många som är så djupt intresserade av det som jag så att jag kan få ett vettigt svar... Hoppas på att Lars Berg som brukar ha koll på standarderna kanske dyker upp här. :)

> Hoppas du har tid att utveckla ditt svar ytterligare, för det här var mycket intressant...

Javisst, en intressant diskussion finns det alltid tid för. :)

> hur anser du att jag borde lösa mitt ursprungliga problem?

En trigger, kanske specifikt en INSTEAD OF-trigger (eftersom du kör SQL Server 2000), skulle kunna lösa det. Eller en annorlunda design (Chris Date hade förstås förespråkat det).

Återkommer när jag fått mer svar...

Det stämmer att SQL-standarden tillåter flera null i en kolumn
som har ett Unique-constraint.

Det står så här:

"A unique constraint is satisfied if and only if no two rows in a table
have the same non-null values in the unique columns".

Dvs, null har ingen betydelse för unique.

"Nja, du har missuppfattat det lite."

Japp, i det exempel som jag tog upp så handlade det ju om heltal så därför förstod jag inte riktigt hur fyrvärdeslogiken skulle appliceras på den. Men nu är det glasklart! Tack!

Om man skulle designa om databasen för mitt exempel, hur skulle man då göra? Exemplet var ju organisationsnummer som måste vara unikt när det anges. Detta ligger i nuläget i vår Customer-tabell. Borde organisationsnummer i st vara en egen tabell och sedan ha foreign keys (hoppas jag använder rätt ord nu) i Customer-tabellen i stället?

/Per Hultqvist

> "A unique constraint is satisfied if and only if no two rows in a table
have the same non-null values in the unique columns"

OK, då var det den definitionen jag sett (i SQL:200n). Jag tyckte dock den var svårtolkad, den skulle kunna tolkas som att man inte får ha null överhuvudtaget i en unique constraint (och just det är väl en feature som ligger utanför Core SQL?), men jag misstänkte att det var såhär man skulle tolka den. Tack för det!

Tja, har du olika typer av customers (där vissa har en viss typ av attribut men andra inte) så går det ju att göra bättre ja.

<citat>
och just det är väl en feature som ligger utanför Core SQL?
</citat>

Ja.