Index unique filtré (ça existe?) ou simple contrainte check ?

Hello,

Dans une DB que je suis train de modéliser, j'ai la table suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
CREATE TABLE [S_PROMO].[T_DETAIL_DEMO_DTD](
    [DTD_ID] [int] IDENTITY(-2147483648,1) NOT NULL,
    [PRM_ID] [int] NOT NULL,
    [CONT_ID] [int] NOT NULL,
    [DTD_PERCENT] [decimal](4, 2) NOT NULL,
    [DTD_DESC] [varchar](200) NULL,
    [DTD_NOTE] [varchar](200) NULL,
    [BA_ID] [tinyint] NOT NULL,
    [DTD_ENCODED] [bit] NOT NULL,
    [PCT_ID] [tinyint] NULL,
    [DTD_CREATED_ON] [datetime] NOT NULL,
    [DTD_CREATED_BY] [varchar](100) NOT NULL,
    [DTD_MODIFIED_ON] [datetime] NULL,
    [DTD_MODIFIED_BY] [varchar](100) NULL,
 CONSTRAINT [PK_T_DETAIL_DEMO_DTD] PRIMARY KEY CLUSTERED 
(
    [DTD_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [AK_T_DETAIL_DEMO_DTD] UNIQUE NONCLUSTERED 
(
    [PRM_ID] ASC,
    [CONT_ID] ASC,
    [DTD_PERCENT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] ADD  CONSTRAINT [DF_T_DETAIL_DEMO_DTD_DTD_ENCODED]  DEFAULT ((0)) FOR [DTD_ENCODED]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] ADD  CONSTRAINT [DF_T_DETAIL_DEMO_DTD_PCT_ID]  DEFAULT ((0)) FOR [PCT_ID]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] ADD  CONSTRAINT [DF__T_DETAIL___DTD_C__40E5634A]  DEFAULT (getdate()) FOR [DTD_CREATED_ON]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] ADD  CONSTRAINT [DF__T_DETAIL___DTD_C__41D98783]  DEFAULT (suser_sname()) FOR [DTD_CREATED_BY]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD]  WITH CHECK ADD  CONSTRAINT [FK_DTD_TO_BA] FOREIGN KEY([BA_ID])
REFERENCES [dbo].[T_BUREAU_ACHAT_BA] ([BA_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] CHECK CONSTRAINT [FK_DTD_TO_BA]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD]  WITH CHECK ADD  CONSTRAINT [FK_DTD_TO_CONT_ID] FOREIGN KEY([CONT_ID])
REFERENCES [S_CONTRAT].[T_CONTRAT_DEMO_CONT] ([CONT_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] CHECK CONSTRAINT [FK_DTD_TO_CONT_ID]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD]  WITH CHECK ADD  CONSTRAINT [FK_DTD_TO_PCT] FOREIGN KEY([PCT_ID])
REFERENCES [S_PROMO].[T_PERCENT_TYPE_PCT] ([PCT_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] CHECK CONSTRAINT [FK_DTD_TO_PCT]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD]  WITH CHECK ADD  CONSTRAINT [FK_DTD_TO_PRM] FOREIGN KEY([PRM_ID])
REFERENCES [S_PROMO].[T_PROMO_PRM] ([PRM_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] CHECK CONSTRAINT [FK_DTD_TO_PRM]
GO

Comme vous pouvez le voir, il y a déjà un clef alternative sur les colonnes PRM_ID, CONT_ID et DTD_PERCENT.
Mais je viens de me rendre compte d'une contrainte supplémentaire qui est que lorsque PCT_ID vaut 0, alors le couple (PRM_ID, CONT_ID) doit être unique.

Je pourrais faire cela via une contrainte check qui necessiterait la création d'une fonction scalaire. Pas de problème pour ça.

Mais dans un souci d'auto formation, je me demandais s'il n'y avait pas plus simple. Un genre d'index unique filtré qui ne s'appliquerait que pour les lignes où PCT_ID serait égal à 0.

Cela est-il possible ?

Merci d'avance.

P.S. : Je sais que les indexes filtrés existent mais je ne les ai encore jamais vraiment utilisés. Par contre, les indexes uniques filtrés, ça j'ignore ^^. Je ne sais même pas si le terme filtré est le bon à utiliser dans ce cas-ci.

Bonjour,

Si, c'est bien entendu possible.
Code:

1 2 CREATE UNIQUE INDEX NomIndex ON T_DETAIL_DEMO_DTD(PRM_ID, CONT_ID) WHERE PCT_ID = 0
Il s'agit bien d'un index unique filtré.
Cela est par exemple bien utile pour contourner le défaut d'implémentation de SQL server au niveau des contraintes UNIQUE qui n'autorise pas plusieurs NULL :
Code:

1 2 CREATE UNIQUE INDEX NomIndex ON TABLE(COLONNE) WHERE COLONNE IS NOT NULL

Merci aieeeuuuuu.

Cela fait donc une corde de plus à mon arc. :)