Discussion :

[msomso]

Bonjour
Comment peut-on gérer l'unicité de valeurs 'not null' d'une colonne 'nullable' ?
La définition d'index unique est impossible sur ce type de colonne.

Peut -on y arriver autrement qu'avec les triggeurs ?

Merci
msomso

[kagemaru]

Bonjour,

Bien sûr que vous pouvez :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create unique index indexname on table(nullablecol)

s'il s'agit d'une contrainte

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

...... constraint PK_xx unique (nullablecol)

.... ou je n'ai pas bien compris votre pb

[msomso]

Bonjour
1. Création d'index unique est refusée pour colonnes nullables.
Il faut peut être modifier une option de serveur, laquelle ?

2. Création de contrainte unique donne ceci:

alter table DM_ANNEXE_DEC add constraint CT_test UNIQUE (LID_L_ID_accuseReception)

Error: Un index comportant les mêmes colonnes dans le même ordre existe déjà sur cette table 'DM_ANNEXE_DEC'.
SQLState: ZZZZZ
ErrorCode: 1921

L'index est créé pour optimiser l'accés et il ne peut pas être unique pour la colonne nullable (refus ASE). Voici son ddl

create nonclustered index LID_ASS1_LID_FK
on mig.dbo.DM_ANNEXE_DEC(LID_L_ID_accuseReception)

Merci
msomso

[mpeppler]

Effectivement, à partir du moment où il y a des nulls dans la colonne on ne peut pas faire un index unique (vu que null = null :-)

Tu peux peut-être faire une contrainte "check".

Michael

[kagemaru]

Effectivement, à partir du moment où il y a des nulls dans la colonne on ne peut pas faire un index unique (vu que null = null :-)

Ahhhh oui en effet, du moment où il y a DES nulls. La question n'est pas vraiment claire au départ

Mais il est possible de créer un index unique sur un colonne 'nullable' à condition qu'il n'y ait une seule valeur nulle.

Sinon une contrainte ou un trigger peut éventuellement faire l'affaire.

[msomso]

Bonjour,
contrainte "check" pour une liste de valeurs, oui, mais pour garantir l'unicité ?
Je ne vois pas comment, auriez-vous un exemple ?

Je voudrais éviter si possible d'écrire des triggeurs (insertion, modification ...).
Certains SGBDR permettent de définir les contraintes UNIQUE sur les colonnes nullable. Et alors le PRIMARY KEY = UNIQUE + NOT NULL.
Les nulls multiples sont ignorées par le test d'unicité.
Peut être qu'en définissant une RULE, on pourrait simuler ce comportement ?

Merci
msomso

P.S.
Je suis désolée d'avoir manqué de clarté au départ de ce post.

[kagemaru]

Les nulls multiples sont ignorées par le test d'unicité.

Dans quel SGBD avez-vu vous ça ????

UNIQUE=valeurs de clé uniques+1 valeur de clé à null
PK=valeurs de clé uniques, pas de null

Il faudrait donner un exemple avec vos tables. Ce genre de contournement ressemble plus à un pb de conception.

[msomso]

Bonjour
J'aurais preféré ne pas citer les noms, mais pour répondre à la question : c'est Oracle par exemple.
Je parle de contrainte UNIQUE et non de PK (primary key).
Dans une base Oracle, on peut parfaitement garantir l'unicité de valeurs significatives tout en ayant plusieurs null dans la colonne en créant la contrainte UNIQUE.

msomso

[kagemaru]

En effet, Oracle permet cela. C'est qqch que je découvre MySQL et Postgres le permettent également. Apparemment, les valeurs NULL ne sont pas considérées égales entr'elles.

D'un point de vue conception, cela me semble bizarre, mais pour contourner certains problèmes cela peut être pratique.

Ce n'est malheureusement pas le cas avec Sybase ou SQL Server.

Merci pour l'info.

[msomso]

Bonjour

J'ai trouvé "column_expression" pour la création d'index:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
create table tt4 (
    a int not null ,
    b int null ,
)
 
alter table mig.dbo.tt4
add PRIMARY KEY CLUSTERED (a)
     on 'default'
 
create unique nonclustered index y 
on mig.dbo.tt4( isnull(b,a) )
with reservepagegap=1 
go

Cela me permet d'avoir un index unique sur ma colonne nullable, car les valeurs de PK se substituent aux valeurs nulls.
C'est lourd, mais plus fiable à mon sens, que d'avoir les triggeurs avec tous les risques de désactivation, non-déclenchement etc.

Que pensez-vous de la perte de performance liée à cette solution ?

msomso
P.S.
Une autre solution c'est d'utiliser une colonne calculée matérialisée, avec comme inconvénient la modification du MPD.

[mpeppler]

C'est une solution intéressante, pour autant que les valeurs de la PK ne peuvent jamais collisionner avec les vraies valeurs de la colonne.

Pour Kagemaru - ASE reconnait le null = null (si ANSI null est faux), et donc ne considère pas que deux null sont différent. Et comme ASE encode le null comme un champ de longueur zero on peut comprendre que le moteur d'indexage ne sait pas faire la différentiation (je pense qu'Oracle, etc ignore simplement les NULLs quand ils génèrent l'index).

Michael

[kagemaru]

je pense qu'Oracle, etc ignore simplement les NULLs quand ils génèrent l'index

c'est exactement ça