Foreign key non respectée

**Batou69** · 08/02/2017, 11h00

Bonjour à tous,

Lors de nos tests fonctionnels hier, j'ai pu constater que nous avons réussi à supprimer des lignes dans une table alors qu'il existe des références dans une autre.
Le résultat est que je me retrouve avec des lignes orphelines qui ne sont plus liées à rien.

voici le ddl des tables et foreign keys :

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
 
 
 
CREATE TABLE [dbo].[COLIS](
	[NO_COLIS] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[DATE_CREATION] [datetime] NOT NULL,
	[NO_TRIEUR] [smallint] NULL,
	[NO_PLAN_TRI] [int] NULL,
 CONSTRAINT [PK_COLIS_NO_COLIS] PRIMARY KEY CLUSTERED 
(
	[NO_COLIS] 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
 
 
CREATE TABLE [dbo].[EVENEMENT_COLIS](
	[NO_EVENEMENT_COLIS] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[NO_COLIS] [bigint] NOT NULL,
	[TYPE_EVENEMENT_COLIS] [tinyint] NOT NULL,
	[DATE_EVENEMENT] [datetime] NOT NULL,
	[TEMPS_TRAITEMENT] [int] NOT NULL,
 CONSTRAINT [PK_EVENEMENT_COLIS_NO_EVENEMENT_COLIS] PRIMARY KEY CLUSTERED 
(
	[NO_EVENEMENT_COLIS] 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
 
ALTER TABLE [dbo].[EVENEMENT_COLIS]  WITH CHECK ADD  CONSTRAINT [FK_EVENEMENT_COLIS_NO_COLIS] FOREIGN KEY([NO_COLIS])
REFERENCES [dbo].[COLIS] ([NO_COLIS])
GO
 
ALTER TABLE [dbo].[EVENEMENT_COLIS] CHECK CONSTRAINT [FK_EVENEMENT_COLIS_NO_COLIS]
GO

Mon test comporte trois processus :
- un qui insère dans la table COLIS des nouveaux enregistrements
- un qui utilise la table colis et qui insère des evenement_colis
- un qui supprime les anciens colis et les evenements qui lui sont rattachés

Il n'y a pas de transactions, seulement des procédures stoquées qui font les insert/update/delete
Je suis sur un serveur SQL 2016 Standard edition v13.0.4001.0 (SP1)

J'ai don cune ligne d'evenement colis mais plus le colis qui lui correspond.

Comment est ce possible ???

Merci !

**SQLpro** · 08/02/2017, 15h12

Donnez nous le code de vos procédures et les exemples de exécutions que vous faites. Il n'existe pas de bug connus dans SQL Server à ce sujet et le moteur respecte parfaitement les règles. Vous devez donc faire une erreur quelque part.

A +

**Batou69** · 08/02/2017, 15h42

Alors j'ai une procedure stoquée qui insère les colis :
(j'ai enelvé les create procédure)

Code :

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

1
2
3
4
5
6
INSERT dbo.COLIS
	( DATE_CREATION, NO_TRIEUR )
OUTPUT
	INSERTED.NO_COLIS
VALUES
	( GETDATE(),1 )

un procédure qui lit la table colis :
Je suis désolé cette requete utilise des colonnes que j'ai supprimées dans la définition de la table fournie par souci de simplification du problème

Code :

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

1
2
3
4
5
6
7
8
SELECT
	COL.NO_COLIS
	, COL.DATE_CREATION
	, COL.NO_TRIEUR
FROM				dbo.COLIS COL
WHERE
	ID_OBJET = @IdObjet
	AND NO_CELLULE = @NoCellule

une qui insère des evenements colis:

Code :

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

1
2
3
4
5
INSERT
	dbo.EVENEMENT_COLIS
	( NO_COLIS, TYPE_EVENEMENT_COLIS, DATE_EVENEMENT, TEMPS_TRAITEMENT )
VALUES
	( @NoColis, @TypeEvenement, GETDATE(), @TempsTraitement )

une qui delete les evenements colis (celle ci est "sale" selon moi et surtout pas optimisée mais c'est ce que j'ai pour l'instant):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
WITH CTE_TABLE AS
	(  
	SELECT EVENEMENT_COLIS.NO_EVENEMENT_COLIS  
	FROM 			dbo.[COLIS]   
	JOIN 			dbo.[EVENEMENT_COLIS] 	ON [EVENEMENT_COLIS].[NO_COLIS] = [COLIS].[NO_COLIS]
	WHERE 
		COLIS.DATE_CREATION < DATEADD(HOUR, -10*24 + 1,CONVERT(datetime, '2017-02-06 16:10:00.002', 121))
	)
DELETE EVENEMENT_COLIS 
FROM 			dbo.[EVENEMENT_COLIS] EVENEMENT_COLIS 
JOIN 			CTE_TABLE 							ON EVENEMENT_COLIS.NO_EVENEMENT_COLIS= CTE_TABLE.NO_EVENEMENT_COLIS

et enfin une qui delete les colis (idem precedente):

Code :

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

1
2
3
4
5
6
7
8
9
10
WITH CTE_TABLE AS
	(
	SELECT COLIS.NO_COLIS
	FROM 				dbo.[COLIS]
	WHERE 
		COLIS.DATE_CREATION < DATEADD(HOUR, -10*24 + 1,CONVERT(datetime, '2017-02-06 16:10:00.002', 121))
	) 
DELETE COLIS
FROM 			dbo.[COLIS] COLIS 
JOIN 			CTE_TABLE 				ON COLIS.NO_COLIS= CTE_TABLE.NO_COLIS

Je n'ai rien de plus en terme d'accès aux tables incriminées.
Je précise que je n'ai pas non plus d'opération de bulk

De plus les foreign keys sont bien enable et trusted (vérifié dans la table sys.foreign_keys)

enfin la requete DBCC CHECKCONTRAINTS retourne bel et bien des soucis :

Code :

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

1
2
3
4
[dbo].[EVENEMENT_COLIS]	[FK_EVENEMENT_COLIS_NO_COLIS]	[NO_COLIS] = '73022175'
[dbo].[EVENEMENT_COLIS]	[FK_EVENEMENT_COLIS_NO_COLIS]	[NO_COLIS] = '73561858'
[dbo].[EVENEMENT_COLIS]	[FK_EVENEMENT_COLIS_NO_COLIS]	[NO_COLIS] = '73621816'
...

A noter que en cours de test, checkconstraint retourne lpsu ou moins de lignes sur cette table.....

Merci de votre aide

Edit : correction du schema des tables dans les requetes

**SQLpro** · 10/02/2017, 13h49

Mettez le code complet car sans la définition de vos variables on ne peut rien faire.

A +

**Batou69** · 10/02/2017, 17h21

Ok,

je m'excuse et j'ai compris la leçon.

Voici un nouveau script qui comporte l'ensemble des elements pour faire le test. La creation des tables, les procedures stoquées et des scripts pour simuler le fonctionnement. J'ai personnellement testé sur mon serveur.

J'ai quatre processus en parallele (les quatre boucles while). Ils sont simulés dans le script et il faut les executer dans des fenetres séparées.
Malheureusement avec cette configuration, je ne reproduit pas le problème maintenant. Et je ne voit pas ce qui est différent.

A noter que l'un des scripts provoque des erreurs de violation de contrainte. Cela n'est pas normal et a été corrigé dans notre simulateur. Cependant je l'ai laissé car le problème est survenu avec ces requetes et les violations. Peut etre est ce une piste.

Merci !

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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
CREATE TABLE [dbo].[COLIS](
	[NO_COLIS] [bigint] IDENTITY(1,1) NOT NULL,
	[DATE_CREATION] [datetime] NOT NULL,
	[NO_TRIEUR] [smallint] NULL,
	[NO_TRACKING] [int] NULL,
	[ID_OBJET] [int] NULL,
	[NO_CELLULE] [int] NULL,
	[NO_INJECTEUR] [smallint] NULL,
	[NO_POSTE_INJECTION] [smallint] NULL,
	[NO_PLAN_TRI] [int] NULL,
	[NO_ETAPE_ALGO] [smallint] NULL,
	[NO_EMPLACEMENT_THEO] [int] NULL,
	[NO_SORTIE_THEO] [int] NULL,
	[NO_SORTIE_REELLE] [int] NULL,
 CONSTRAINT [PK_COLIS_NO_COLIS] PRIMARY KEY CLUSTERED 
(
	[NO_COLIS] 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
 
CREATE TABLE [dbo].[EVENEMENT_COLIS](
	[NO_EVENEMENT_COLIS] [bigint] IDENTITY(1,1) NOT NULL,
	[NO_COLIS] [bigint] NOT NULL,
	[TYPE_EVENEMENT_COLIS] [tinyint] NOT NULL,
	[DATE_EVENEMENT] [datetime] NOT NULL,
	[TEMPS_TRAITEMENT] [int] NOT NULL,
	[NO_EQUIPEMENT] [int] NULL,
	[NO_UTILISATEUR] [int] NULL,
 CONSTRAINT [PK_EVENEMENT_COLIS_NO_EVENEMENT_COLIS] PRIMARY KEY CLUSTERED 
(
	[NO_EVENEMENT_COLIS] 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
 
ALTER TABLE [dbo].[EVENEMENT_COLIS]  WITH CHECK ADD  CONSTRAINT [FK_EVENEMENT_COLIS_NO_COLIS] FOREIGN KEY([NO_COLIS])
REFERENCES [dbo].[COLIS] ([NO_COLIS])
GO
 
ALTER TABLE [dbo].[EVENEMENT_COLIS] CHECK CONSTRAINT [FK_EVENEMENT_COLIS_NO_COLIS]
GO
 
CREATE PROCEDURE dbo.PontClient_InsertParcel_100
AS
	INSERT dbo.COLIS
		( DATE_CREATION, NO_TRIEUR )
	VALUES
		( GETDATE(),1 )
GO
 
CREATE PROCEDURE dbo.Arche_UpdateParcel_100
											@NoColis AS BIGINT
											, @IdObject AS INT
											, @NoCell AS INT
AS
	UPDATE dbo.COLIS
	SET
		NO_CELLULE = @NoCell
		, ID_OBJET = @IdObject
	WHERE
		NO_COLIS = @NoColis
GO
 
CREATE PROCEDURE [dbo].Trieur_SelectColisFromIdObjetCell_100
											@IdObjet as int
											, @NoCellule as int
AS
	SELECT
		COL.NO_COLIS
		, COL.DATE_CREATION
		, COL.NO_TRIEUR
		, COL.NO_TRACKING
		, COL.ID_OBJET
		, COL.NO_CELLULE
		, COL.NO_INJECTEUR
		, COL.NO_POSTE_INJECTION
		, COL.NO_PLAN_TRI
		, COL.NO_ETAPE_ALGO
		, COL.NO_EMPLACEMENT_THEO
		, COL.NO_SORTIE_THEO
		, COL.NO_SORTIE_REELLE
	FROM				dbo.COLIS COL
	WHERE
		ID_OBJET = @IdObjet
		AND NO_CELLULE = @NoCellule
GO
 
CREATE PROCEDURE [dbo].Trieur_InsertEvenementColis_100
														@NoColis as bigint
														, @TypeEvenement as tinyint
														, @TempsTraitement as int
AS
	INSERT
		dbo.EVENEMENT_COLIS
		( NO_COLIS
		, TYPE_EVENEMENT_COLIS
		, DATE_EVENEMENT
		, TEMPS_TRAITEMENT )
	VALUES
		( @NoColis
		, @TypeEvenement
		, GETDATE()
		, @TempsTraitement )
GO
 
CREATE PROCEDURE [dbo].Trieur_PurgeEvenementColis_100
AS
	WITH CTE_TABLE AS
		(  
		SELECT EVENEMENT_COLIS.NO_EVENEMENT_COLIS  
		FROM 			dbo.[COLIS]   
		JOIN 			dbo.[EVENEMENT_COLIS] 	ON [EVENEMENT_COLIS].[NO_COLIS] = [COLIS].[NO_COLIS]
		WHERE 
			COLIS.DATE_CREATION < DATEADD(minute, -10, GETDATE() )
		)
	DELETE EVENEMENT_COLIS 
	FROM 			dbo.[EVENEMENT_COLIS] EVENEMENT_COLIS 
	JOIN 			CTE_TABLE 							ON EVENEMENT_COLIS.NO_EVENEMENT_COLIS= CTE_TABLE.NO_EVENEMENT_COLIS
GO
 
CREATE PROCEDURE [dbo].Trieur_PurgeColis_100
AS
	WITH CTE_TABLE AS
		(
		SELECT COLIS.NO_COLIS
		FROM 				dbo.[COLIS]
		WHERE 
			COLIS.DATE_CREATION < DATEADD(minute, -10,GETDATE() )
		) 
	DELETE COLIS
	FROM 			dbo.[COLIS] COLIS 
	JOIN 			CTE_TABLE 				ON COLIS.NO_COLIS= CTE_TABLE.NO_COLIS
GO
 
 
 
-- decouper ici 
---------------------------------------------------------
-- pont client
-- generation de lignes dans la table colis
-- taux : 60 colis par seconde
WHILE ( 1 = 1 )
BEGIN
	EXEC dbo.PontClient_InsertParcel_100 
	WAITFOR DELAY '00:00:00.016' -- wait 16 milliseconds (pour avoir environ 60 colis/seconde)
END
 
 
-- decouper ici 
---------------------------------------------------------
-- arche
-- selection d'un colis au hazar
-- mise a jour du colis avec les colonnes ID_OBJET et CELL
-- et insertion d'evenement colis
-- taux : 9 update et insert par seconde 
DECLARE @IdObject INT = 0
DECLARE @NoCell INT = FLOOR( RAND() * 500 )
DECLARE @NoColis BIGINT
DECLARE @Temps INT 
 
WHILE ( 1 = 1 )
BEGIN
	-- a chaque boucle augmenter @IdObject de 1
	SET @IdObject = @IdObject + 1
	SET @Temps = FLOOR( RAND() * 300 )
 
	-- faire une selection aleatoire du prochain colis
	SELECT TOP 1
		@NoColis = NO_COLIS
	FROM						COLIS
	WHERE 
		(ABS(convert(int, BINARY_CHECKSUM( NO_COLIS, NEWID() ) ) )  % 100 ) = 50
		AND ID_OBJET IS NULL
 
	-- ensuite mise a jour du colis
	EXEC dbo.Arche_UpdateParcel_100 @NoColis, @IdObject, @NoCell
 
	-- et insertion d'un evenement
	EXEC dbo.Trieur_InsertEvenementColis_100 @NoColis, 20, @Temps
 
	WAITFOR DELAY '00:00:00.111' -- (pour avoir environ 9 insert et update/seconde)
END
 
 
-- decouper ici 
---------------------------------------------------------
-- purge
-- suppression des evenements colis et des colis
-- taux : une fois par minute
WHILE ( 1 = 1 )
BEGIN
	EXEC dbo.Trieur_PurgeEvenementColis_100
	EXEC dbo.Trieur_PurgeColis_100
	WAITFOR DELAY '00:01:00.000'
END
 
 
-- decouper ici 
---------------------------------------------------------
-- trieur
-- selection de ligne dans la table colis 
-- insertion d'evenement colis
-- taux : 15 insert par seconde
DECLARE @NoColis AS BIGINT
DECLARE @Temps INT
 
WHILE ( 1 = 1 )
BEGIN
	-- faire une selection aleatoire du prochain colis
	SELECT TOP 1
		@NoColis = NO_COLIS
	FROM						COLIS
	WHERE 
		(ABS(convert(int, BINARY_CHECKSUM( NO_COLIS, NEWID() ) ) )  % 100 ) = 50
		AND ID_OBJET IS NOT NULL
	SET @Temps = FLOOR( RAND() * 300 )
 
	EXEC dbo.Trieur_InsertEvenementColis_100 @NoColis , 4, @Temps
	WAITFOR DELAY '00:00:00.066' -- (pour avoir environ 15 insert/seconde)
END

**SQLpro** · 14/02/2017, 16h11

Votre dernière boucle part en erreur :
Msg*515, Niveau*16, État*2, Procédure*Trieur_InsertEvenementColis_100, Ligne*214
Impossible d'insérer la valeur NULL dans la colonne 'NO_COLIS', table 'DB_DEVELOPPEZ.dbo.EVENEMENT_COLIS'. Cette colonne n'accepte pas les valeurs NULL. Échec de INSERT.

Cependant,je ne voit aucune ligne orphelines un fois ce traitement terminé. Voici la requête que j'utilise :

Code :

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

1
2
3
4
5
SELECT *
FROM   [dbo].[EVENEMENT_COLIS] AS EC
       RIGHT OUTER JOIN [dbo].[COLIS] AS C
             ON EC.[NO_COLIS] = C.[NO_COLIS]
WHERE  C.[NO_COLIS] IS NULL;

Semi anti jointure droite...

A mon avis, c'est votre façon de contrôler la chose qui n'est pas bonne !

A +

Foreign key non respectée

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager