[SQL] UNIQUE...

[Mantis-89]

Ciao, a tutti.
sto convertendo alcune tabelle da FoxPro a SQL utilizzando SQL Server. In FoxPro ho un campo che se non è vuoto deve essere univoco*, c'è un modo in SQL per ricreare la stessa situazione?
So che utilizzando la clausola UNIQUE non posso avere valori doppi ma questo vale anche se è vuoto.
Qualcuno ha qualche idea?

Grazie in anticipo.


* In foxpro il campo è reso univoco dall'indice, che ha come filtro la condizione !EMPTY(...) per cui è univoco solo se non è vuoto.

[gugoXX]

Quello che chiedi in SqlServer non si puo' fare in modo naturale perche' NULL e' un valore come un altro, e quindi non si possono inserire piu' NULL nella stessa colonna con vincolo unique.
(PS: Ritengo che il comportamento di altri engine, come ORACLE o appunto FoxPro sia quello corretto).

Pero' esiste un workaround:

Innanzitutto la tabella deve disporre di una colonna univoca a singola colonna.
Quindi o c'e' gia' una chiave primaria/colonna univoca oppure occorre metterla con una IDENTITY(1,1)

Chiamiamo COLONNA1 la colonna target, ovvero quella per la quale si vorrebbe il vincolo di univocita' come sotto Oracle, FoxPro, etc.
Occorre inserire una nuova colonna calcolata, chiamiamola SURRUK, che varra' COLONNA1 quando COLONNA1 non e' NULL, altrimenti varra' il valore della colonna univoca.

Il calcolo e' quindi
CASE WHEN COLONNA1 != NULL THEN COLONNA1 ELSE **UK** END

Per costruzione, quando si inserira' una riga con null, allora SURRUK conterra' un valore sicuramente univoco.
Quando invece si inserira' qualcosa in Colonna1, allora SURRUK varra' tale valore.
Su SURRUK si potra' aggiungere un constraint di univocita' che si comportera' proprio come desiderato.

Esempio con chiave primaria gia' esistete di tipo numerico, e con colonna target "Colonna1" di tipo testuale

create table prpr
(
    veraPK int identity(1,1),
    Colonna1 varchar(30),    
    SURRUK as CASE WHEN Colonna1 IS NOT NULL THEN Colonna1 ELSE CAST( veraPK AS VARCHAR(30)) END,
)

CREATE UNIQUE INDEX IU_PRPR_SURRUK ON prpr(SURRUK);

insert into prpr(Colonna1) values('abc');
insert into prpr(Colonna1) values('def');
insert into prpr(Colonna1) values(NULL);
insert into prpr(Colonna1) values(NULL);
-- fin qui tutto OK!!!

insert into prpr(Colonna1) values('abc');
-- Qui si schianta.
-- Schiantare e' il verbo tecnico usato in questi casi.

[Mantis-89]

Ok, sembra ottimo adesso provo
Dovrebbe funzionare anche per stringhe vuote che non sono null, vero? Basta cambiare la condizione in

SURRUK as CASE WHEN Colonna1 IS NOT NULL AND Colonna1 <> '' THEN Colonna1 ELSE CAST( veraPK AS VARCHAR(30)) END,

Quando ho scritto vuoto intendevo '' e non NULL. Teoricamente anche FoxPro si dovrebbe comportare come SQL, il fatto è che gli indici posso avere un filtro sui dati a cui devono essere applicati e l'indice che utilizziamo è di tipo candidate (non ammette valori duplicati) però è filtrato per valori non vuoti.

Cmq dire che il foxpro si comporti in modo corretto è bella grossa

Ti ringrazio, ciao!

[gugoXX]

Si', funziona anche per il nuovo comportamento da te descritto.

Che SQLServer (e altri) si comporti in modo errato e invece Oracle (e FoxPro e altri) invece in modo giusto e' arbitrario, nel senso che l'unica direttiva del SQL e' stata che NULL deve essere trattato come "Assenza di valore".

La domanda se su una colonna univoca possano essere presenti piu' record con "Assenza di valore" resta ambigua, e non chiaramente definita dallo stadard.

Comunque, per la cronaca, in SQL Server 2008 e' stato aggiunto il concetto di indici e constraint filtrati.
Per esempio potrei voler aggiungere un constraint di univocita' su una colonna, valente pero' solo per i valori tra 1000 e 2000.
Con questi nuovi oggetti e' quindi possibile descrivere esattamente il comportamento da te desiderato.

[Mantis-89]

Cmq dire che il foxpro si comporti in modo corretto è bella grossa

Scusa forse, mi sono espresso male in quanto non era una critica riferita a te ma al FoxPro, che ha molti pregi, ma ha anche tanti difetti...

Interessante questa nuova caratteristica di SQL Server 2008, purtroppo abbiamo già rimpianto il fatto di non averlo messo come requisito minimo per il nostro programma... ci faciliterebbe di molto il passaggio da Fox a SQL

Ti ringrazio ancora per la disponibilità e l'aiuto,

[gugoXX]

Originariamente inviato da Mantis-89

Scusa forse, mi sono espresso male in quanto non era una critica riferita a te ma al FoxPro, che ha molti pregi, ma ha anche tanti difetti...

Avevo capito bene, non mi era neppure passata l'idea che fosse una critica rivolta "a me".
Peraltro FoxPro non lo conosco neppure tanto bene, l'ho usato solo una volta per lavoro, e non mi sbilancerei con opinioni sul motore.

PS: La migrazione da SqlServer2005 a SqlServer2008 e' abbastanza indolore. Rischia addirittura di essere trasparente, a differenza di quella dal 2000 al 2005.

Buon lavoro.

[Mantis-89]

Premettoc he non ho ancora provato, però ci ho pensato un attimo e la cosa ha la limitazione che se inserisco dei numeri la cosa potrebbe non funzionare, o sbaglio?

Per ovviare a questo credo sia:

 SURRUK as CASE WHEN Colonna1 IS NOT NULL THEN CAST( veraPK AS VARCHAR(30)) + Colonna1 ELSE CAST( veraPK AS VARCHAR(60)) END

giusto?
Grazie, Andrea.

[gugoXX]

Originariamente inviato da Mantis-89

Premettoc he non ho ancora provato, però ci ho pensato un attimo e la cosa ha la limitazione che se inserisco dei numeri la cosa potrebbe non funzionare, o sbaglio?

Per ovviare a questo credo sia:

 SURRUK as CASE WHEN Colonna1 IS NOT NULL THEN CAST( veraPK AS VARCHAR(30)) + Colonna1 ELSE CAST( veraPK AS VARCHAR(60)) END

giusto?
Grazie, Andrea.

Se la colonna originale e' una stringa, allora non potrai inserirci un numero nemmeno volendo.

Se invece la colonna originale e' un numero, allora bastera' che anche surruk sia un numero, senza quindi dover castare piu' nulla, e lo statement sarebbe

SURRUK as CASE WHEN Colonna1 IS NOT NULL THEN Colonna1 ELSE ChiaveUnivoca END

Ovviamente ChiaveUnivoca deve essere un numero univoco.
Quindi o la chiave primaria e' un numero univoco, e quindi usi quella, oppure aggiungi una colonna di sola lettura
ChiaveUnivoca int IDENTITY(1,1)
e la usi per questo scopo.

[Mantis-89]

ma se io inserisco '12' come valore di colonna1 ed è il 13 record, e il 12 record ha colonna1 a NULL il cast di veraPK non è sempre '12'? Non mi vede in questo modo un valore già inserito quando non lo è?
Se poi Colonna1 fose un numero la cosa sarebbe anche peggio no?

[gugoXX]

Originariamente inviato da Mantis-89

ma se io inserisco '12' come valore di colonna1 ed è il 13 record, e il 12 record ha colonna1 a NULL il cast di veraPK non è sempre '12'? Non mi vede in questo modo un valore già inserito quando non lo è?
Se poi Colonna1 fose un numero la cosa sarebbe anche peggio no?

Si' giusto.
Propongo, se il dominio di colonna1 fosse solo tra valori positivi, puoi usare -veraPK come valore nel case.

[Mantis-89]

Propongo, se il dominio di colonna1 fosse solo tra valori positivi, puoi usare -veraPK come valore nel case.

Però io sono un tipo molto pessimistico... E cmq (nel mio caso) nella colonna ci deve finire un codice inserito dall'utente. Può contenere caratteri, numeri e anche alcuni simboli(anche '-' ).
E ora che ci penso quanto ho scritto poco fa non funzionerebbe nel senso che farebbe più quello che noi ci proponiamo....
Si fa interessante come problema che dici..?
Cmq forse ho capito come fare:

SURRUK as CASE WHEN Colonna1 IS NOT NULL AND Colonna1 <> '' THEN 'X' + Colonna1 ELSE CAST( veraPK AS VARCHAR(31)) END

Se quando aggiungo una lettera fissa davanti alla mia colonna so per certo che non avrò mai un un valore che possa essere restituito dall'operazione di CAST.

[Mantis-89]

Provato e sembra funzionare perfettamente!!
Ti ringrazio!

Volevo chiederti solo un unica cosa: nel tuo esempio creavi un indice univoco, che differenza c'è tra creare un indice univoco come hai fatto tu e specificare UNIQUE quando dichiaro il campo SURRUK? (mi sembra che venga creato lo stesso, cambia solo che non sono io a decidere il nome dell'indice?)

PS: SURRUK ha qualche significato particolare?

Ancora grazie

[gugoXX]

Originariamente inviato da Mantis-89

Provato e sembra funzionare perfettamente!!
Ti ringrazio!

Volevo chiederti solo un unica cosa: nel tuo esempio creavi un indice univoco, che differenza c'è tra creare un indice univoco come hai fatto tu e specificare UNIQUE quando dichiaro il campo SURRUK? (mi sembra che venga creato lo stesso, cambia solo che non sono io a decidere il nome dell'indice?)

Esatto, funzionalmente non cambia niente.
E' bene pero' avere il nome a disposizione, nel caso in cui volessero alterare o cancellare (e ci si riferisce per nome)

PS: SURRUK ha qualche significato particolare?

Ancora grazie

Surrogate Unique Key.