1 spg til SQL experten

UNIQUE constraints kan ikke anvendes, hvis det skal være tilladt at indsætte flere rækker der er identiske. NULL værdier er i denne sammenhæng også en del af uniqueness, så flere rækker med NULL i alle ovenstående felter, vil være et brud på constraint'en.

Jeg vil i stedet anbefale dig at lave en INSERT trigger, der kontrollerer om den indsatte post allerede findes i databasen. I så fald bruger du RAISERROR/ROLLBACK TRANSACTION til at afvise den indsatte post.

Hvis det er SQL Server 2000 du anvender er der en elegant løsning:

Med SQL Server 2000 blev det muligt at lave index på view's. Lav derfor et view hvor du sorterer række med NULL fra. Herefter laver du index på dette view, som sikre at du får unikke værdier. Lettere kan det vist ikke være :o)

- sunedh

Måske har jeg misforstået spørgsmålet, men det løser vel ikke problemet ? Jeg opfattede spørgsmålet som om vedkommende ønskede en constraint. Hvis det er korrekt opfattet, hjælper det ikke meget at sortere det fra i et view eller ?

Jeg håber det er dig der har opfattet det forkert (ellers er det jo mig :o)

Måske forklarede jeg det ikke godt nok, så jeg prøver lige igen:
1. Lav et view, der sorterer rækker med NULL værdier fra
2. Lav et unique index på dette view. Herved vil alle værdier i tabellen, som ikke er NULL være unikke.

Som sagt er det nyt i SQL Server 2000 at man kan lave index på views og der står meget mere om det i BOL.

Sig endelig til hvis der stadig er noget, som ikke er klart - eller det er mig der har misforstået det :o)

- sunedh

ehhhh..... shit... triggers og view og jeg skal gi dig....
Det jeg oensker er at der IKKE kan blive sat to ens kolonner ind i denne tabel mm. x,y,z vaerdierne er NULL.

Jeg ved ikke om det er det du proever paa sunedh.... men hvis det er det.... ehhh, saa forstaar jeg ikke det du skriver.

tmceu... jeg forstaar at du vil have at jeg skal lave en "instead of insert" trigger.....

Jeg har kigget lidt paa det, men kan ikke lige se hvordan det kan goeres med triggers. Jeg skal paa en eller anden maade sige: Hvis der er en vaerdi i forvejen hvor x,y,z er forskellig for null, saa skal den raekke der indsaettes nu have vaerdien null paa x,y og z's plads.

Samtidig med dette er der primary key... hvad sker der hvis man indsaetter noget vha. en trigger og det man indsaetter bryder unikheden i primary keyen?

Her er et lille eksempel på brug af trigger. Jeg har ikke lavet det med din seneste kommentar omkring replace af værdier med NULL. Men hvis du kan se ideen, bør du selv kunne rette det til.

Og bare rolig, PK constraints bliver stadig håndhævet.

CREATE TABLE MyTable(

    MyID    int IDENTITY NOT NULL,
    x    varchar(10) NULL,
    y    varchar(10) NULL,
    z    varchar(10) NULL,

    PRIMARY KEY(MyID)
)

CREATE TRIGGER MyTable_I ON MyTable INSTEAD OF INSERT AS

DECLARE @x varchar(10), @y varchar(10), @z varchar(10)

SELECT    @x = x,
    @y = y,
    @z = z
FROM Inserted

IF EXISTS (SELECT TOP 1 * FROM MyTable WHERE x=@x AND y=@y AND z=@z)
BEGIN
    RAISERROR ('Violation of uniqueness', 16, 1)
    ROLLBACK TRANSACTION
END
ELSE
BEGIN
    INSERT INTO MyTable(x, y, z) VALUES(@x, @y, @z)
END

Prøv herefter at køre flg. statement flere gange. 2. og efterfølgende gang skulle gerne afvise den indsatte række.

INSERT INTO MyTable(x, y, z) VALUES('a', 'b', 'c')

Jeg har nu ikke tænkt mig sådan at give mig - jeg mener stadig at min løsning er den mest elegante, men okey jeg må medgive at jeg ikke er god til at forklare den :o)

Vi har tabellen :

CREATE TABLE Cars (
  carcode INT,
  x INT,
  y INT,
  z INT
)

Jeg ved ikke om datatyperne er de rigtige, men det er ligemeget for eksemplet. Og som det er tilfældet med tmceu's løsning har dette ingen indflydelse på hvad der ellers måtte være af constraints.

Herefter laver vi et view på tabellen Cars. Views kaldes også somme tider for virtuelle tabeller, hvilket kan være rart at vide når man skal forstå hvad det er. Du kan læse meget mere om views i SQL Server Books Online.

CREATE VIEW vCars AS
  SELECT *
  FROM Cars
  WHERE x IS NOT NULL
  AND y IS NOT NULL
  AND z IS NOT NULL

Ved at skrive SELECT * FROM vCars får vi nu alle biler, hvor x, y og z IKKE er NULL. Jeg kan ikke helt gennemskue ud fra hvad du skriver om AND i WHERE-delen skulle have været en OR, men det ved du nok selv.

Nu skal vi så have sikret at man ikke kan indsætte to række hvor x, y og z har samme værdi, med mindre de er NULL. Det gør vi ved at lave et UNIQUE index på de tre kolonner i det view vi har lavet:

CREATE UNIQUE NONCLUSTERED INDEX iCars
ON vCars (x, y, z)

Da indexet er UNIQUE sikre det at man ikke kan indsætte to rækker i vCars (og reelt dermed i Cars), hvor x, y og z er ens.

Det synes jeg altså er smart :o) Som sagt virker det kun i SQL Server 2000. Jeg har ikke testet kode, så det er muligt at der er en enkelt slå fejl - håber det går.

Håber jeg nu har gjort min løsning forståelig for alle :o)

- sunedh

ok, det er nu soendag aften, og jeg vil kigge paa det i morgen mandag...

sunedh>> Jeg forstaar nu det du vil, hvilket virker rimelig smart

Jamen jeg vil heller ikke sådan lige give mig :-)

Hvis vi henholder os til flg. fra det oprindelige spørgsmål: "NÅR det skal vaere tilladt at indsætte flere raekker med NULL vaerdier i x, y og z."

Det foreslåede index iCars, forhinder ganske rigtigt at man kan indsætte flere rækker med samme værdier i x, y, z og det gør det rigtig effektivt. Så effektivt at man heller ikke kan indsætte flere rækker med NULL i alle og det er vel meningen at man skal kunne jvf. spørgsmålet ?

Man kan jo netop indsætte flere rækker med NULL. De kommer i tabellen men er sorteret fra i view'et.

En UNIQUE constraint, som tmceu snakkede om i det første svar, implementeres jo rent teknisk ved at der netop laves et index. Data, som ikke er med i vCars har iCars ingen indflydelse på.

- sunedh

Hvis x,y,z alle kan være NULL, men i øvrigt er en logisk del af en UNIQUE-constraint, så vil jeg anbefale at du prøver at normalisere din tabel.
Det lyder i hvertfald som om at x,y,z er fremmed-nøgler, eller bør være det. Derfor skrider logikken når du vil tilføre en unique-constraint.

Jeg tror faktisk at vi er ret enige i alle de tekniske termer, jeg tror måske "uenigheden" går på en lidt varierende opfattelse af spørgsmålet.

Anyway mener jeg ikke der er grund til at vade mere rundt i det. Ud fra de forskellige muligheder og kommentarer der er præsenteret, bør spørgeren kunne implementere sin løsning, uanset hvordan det så bliver.

Slut herfra :-)

kichian... nej, det er nemlig ikke lige saa nemt at lave en ny tabel herfra... da der er flere constaints en bare denne i tabellen, som ogsaa skal overholdes(alle kolonner er paa en eller anden maade afhaengige af hinanden)... det er lidt indviklet, men saadan er det desvaerre...

Kigger som sagt paa det idag, og jo, der er rigelig mulighed for at finde en loesning.

Hvis flere/alle kolonner er afhængige af hinanden på et eller andet niveau, så skulle den tabel have været normaliseret til 3-normalform for lang tid siden. Trist, og godt det ikke er min hovedpine ;-)

kichian... det er ikke muligt...

sunedh>>
jeg ser nogen muligheder i denne metode... kan man ogsaa f.eks. sige uddriv dette (et eller andet) i et view, hvor en af kolonnerne er sat til NOT NULL???
I det der indsaettes et eller andet, saa checkes det via viewet?