Problème de contrainte d'unicité

**Kropernic** · 24/06/2013, 11h08

Bonjour,

Je rentre directement dans le vif du sujet avec le code DDL des 3 tables suivantes :

Code :

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

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
CREATE TABLE [dbo].[T_STORE_STR](
    [STR_ID] [tinyint] IDENTITY(1,1) NOT NULL,
    [STR_CODE] [char](3) NOT NULL,
    [STR_NAME] [varchar](20) NOT NULL,
    [STR_CREATED_ON] [datetime] NOT NULL,
    [STR_CREATED_BY] [varchar](100) NOT NULL,
    [STR_MODIFIED_ON] [datetime] NULL,
    [STR_MODIFIED_BY] [varchar](100) NULL,
 CONSTRAINT [PK_T_STORE_STR] PRIMARY KEY CLUSTERED 
(
    [STR_ID] 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 [dbo].[T_STORE_STR] ADD  DEFAULT (getdate()) FOR [STR_CREATED_ON]
GO
 
ALTER TABLE [dbo].[T_STORE_STR] ADD  DEFAULT (suser_sname()) FOR [STR_CREATED_BY]
GO
 
CREATE TABLE [dbo].[T_FLOOR_FLO](
    [FLO_ID] [tinyint] IDENTITY(1,1) NOT NULL,
    [FLO_MAP] [varchar](255) NOT NULL,
    [STR_ID] [tinyint] NOT NULL,
    [FLO_NAME] [varchar](20) NOT NULL,
    [FLO_ACTIVE] [bit] NOT NULL,
    [FLO_CREATED_ON] [datetime] NOT NULL,
    [FLO_CREATED_BY] [varchar](100) NOT NULL,
    [FLO_MODIFIED_ON] [datetime] NULL,
    [FLO_MODIFIED_BY] [varchar](100) NULL,
 CONSTRAINT [PK_T_FLOOR_FLO] PRIMARY KEY CLUSTERED 
(
    [FLO_ID] 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 [dbo].[T_FLOOR_FLO]  WITH CHECK ADD  CONSTRAINT [FK_T_FLOOR_FLO_T_STORE_STR] FOREIGN KEY([STR_ID])
REFERENCES [dbo].[T_STORE_STR] ([STR_ID])
ON UPDATE CASCADE
GO
 
ALTER TABLE [dbo].[T_FLOOR_FLO] CHECK CONSTRAINT [FK_T_FLOOR_FLO_T_STORE_STR]
GO
 
ALTER TABLE [dbo].[T_FLOOR_FLO] ADD  CONSTRAINT [DF_T_FLOOR_FLO_FLO_ACTIVE]  DEFAULT ((1)) FOR [FLO_ACTIVE]
GO
 
ALTER TABLE [dbo].[T_FLOOR_FLO] ADD  CONSTRAINT [DF__T_FLOOR_F__FLO_C__08B54D69]  DEFAULT (getdate()) FOR [FLO_CREATED_ON]
GO
 
ALTER TABLE [dbo].[T_FLOOR_FLO] ADD  CONSTRAINT [DF__T_FLOOR_F__FLO_C__09A971A2]  DEFAULT (suser_sname()) FOR [FLO_CREATED_BY]
GO
 
CREATE TABLE [dbo].[T_AREA_ARE](
    [ARE_ID] [int] IDENTITY(1,1) NOT NULL,
    [ARE_NAME] [varchar](5) NOT NULL,
    [ARE_X] [decimal](5, 2) NOT NULL,
    [ARE_Y] [decimal](5, 2) NOT NULL,
    [FLO_ID] [tinyint] NOT NULL,
    [ARE_CREATED_ON] [datetime] NOT NULL,
    [ARE_CREATED_BY] [varchar](100) NOT NULL,
    [ARE_MODIFIED_ON] [datetime] NULL,
    [ARE_MODIFIED_BY] [varchar](100) NULL,
 CONSTRAINT [PK_T_AREA_ARE] PRIMARY KEY CLUSTERED 
(
    [ARE_ID] 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 [dbo].[T_AREA_ARE]  WITH CHECK ADD  CONSTRAINT [FK_T_AREA_ARE_T_FLOOR_FLO] FOREIGN KEY([FLO_ID])
REFERENCES [dbo].[T_FLOOR_FLO] ([FLO_ID])
ON UPDATE CASCADE
GO
 
ALTER TABLE [dbo].[T_AREA_ARE] CHECK CONSTRAINT [FK_T_AREA_ARE_T_FLOOR_FLO]
GO
 
ALTER TABLE [dbo].[T_AREA_ARE] ADD  DEFAULT (getdate()) FOR [ARE_CREATED_ON]
GO
 
ALTER TABLE [dbo].[T_AREA_ARE] ADD  DEFAULT (suser_sname()) FOR [ARE_CREATED_BY]
GO

Je pense que les noms sont assez parlant mais, en gros, on a donc des zones qui sont définies sur des étages qui appartiennent à des magasins.

Ce que je voudrais exprimer comme contrainte, c'est que le nom d'une zone (colonne ARE_NAME) doit être unique par magasin.

Si j'avais la colonne STR_ID directement dans la table T_AREA_ARE, pas de souci. Mais là, ce n'est pas le cas.

Comment faire alors ? Faut-il obligatoirement passer par un trigger ?

**SQLpro** · 25/06/2013, 17h59

trigger obligatoire.

A +

**Kropernic** · 25/06/2013, 18h00

Mince... J'espérais une fonctionnalité dont j'ignorais l'existence pour éviter le trigger.

Quoi qu'il en soit, merci pour la confirmation.

**Kropernic** · 26/06/2013, 12h00

Juste un dernier message pour demander une validation du code de ce trigger car j'ai encore parfois du mal avec la logique ensembliste... (quand on a été formater à traiter les choses de manières itératives, c'est pas facile de changer).

Voici donc le code que j'ai produit :

Code :

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

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
 
CREATE TRIGGER DBO.TR_ARE_INSTEAD_INSERT
   ON  DBO.T_AREA_ARE
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;
 
    IF EXISTS    (
                SELECT
                        *
                FROM
                        dbo.T_AREA_ARE ARE
                            INNER JOIN    dbo.T_FLOOR_FLO FLO
                                ON    ARE.FLO_ID = FLO.FLO_ID
                                INNER JOIN    dbo.T_STORE_STR STR
                                    ON    FLO.STR_ID = STR.STR_ID
                WHERE
                        STR.STR_ID IN    (
                                        SELECT    STR1.STR_ID 
                                        FROM    dbo.T_STORE_STR STR1
                                                    INNER JOIN    dbo.T_FLOOR_FLO FLO1
                                                        ON    STR.STR_ID = FLO1.STR_ID
                                        WHERE
                                                FLO_ID = INSERTED.FLO_ID
                                        )
                    AND ARE_NAME = INSERTED.ARE_NAME
                )
        RAISERROR('EXISTING AREA NAME IN STORE',16,1)
    ELSE
        INSERT INTO DBO.T_AREA_ARE(ARE_NAME, ARE_X, ARE_Y, FLO_ID)
        SELECT
                ARE_NAME,
                ARE_X,
                ARE_Y,
                FLO_ID
        FROM
                INSERTED
END
GO

J'ai l'impression qu'il n'est valable que si on insère qu'une ligne à la fois :-/

EDIT :
En fait, j'ai carrément une erreur à la création du trigger. SQL SERVER ne semble pas connaitre inserted

.

Msg 4104, Level 16, State 1, Procedure TR_ARE_INSTEAD_INSERT, Line 24
The multi-part identifier "INSERTED.FLO_ID" could not be bound.
Msg 4104, Level 16, State 1, Procedure TR_ARE_INSTEAD_INSERT, Line 26
The multi-part identifier "INSERTED.ARE_NAME" could not be bound.

J'imagine que c'est probablement moi qui fait quelque chose de travers mais quoi...

**aieeeuuuuu** · 27/06/2013, 17h01

Bonjour,

Envoyé par Kropernic

Mince... J'espérais une fonctionnalité dont j'ignorais l'existence pour éviter le trigger.

Il y en a bien une : les vues indexée !
Vous pouvez créer une vue faisant une jointure sur les trois tables, avec un index unique sur les colonnes STR_ID et ARE_NAME :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE VIEW V_STR_FLO_ARE
WITH SCHEMABINDING
AS
  SELECT S.STR_ID,A.ARE_NAME
  FROM	dbo.T_STORE_STR S
  INNER JOIN dbo.T_FLOOR_FLO F
	ON		F.STR_ID = S.STR_ID
  INNER JOIN dbo.T_AREA_ARE A
	ON		A.FLO_ID = F.FLO_ID

Code :

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

1
2
3
 
CREATE UNIQUE CLUSTERED INDEX CIX_V_U
ON V_STR_FLO_ARE(STR_ID, ARE_NAME)

**Kropernic** · 28/06/2013, 08h30

J'ai fait mon trigger du coup...

.

Mais je note car ça pourrait alléger le code d'un paquet de procédure stockée.

Au niveau des performances, lequel l'emporte ? Le trigger ou la vue indexée ?

Problème de contrainte d'unicité [2008R2]

Administration SQL Server

Discussions similaires

Partager

Partager