[Foreign Key] Besoin d'explication. [Résolu]

[Andry]

Salut à tous,

J'utilise deux tables T_VILLES et T_CLIENTS
Voici les définitions :

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
 
CREATE DOMAIN D_CHAR10 AS CHAR(10);
CREATE DOMAIN D_CHAR15 AS VARCHAR(15);
CREATE DOMAIN D_CHAR50 AS VARCHAR(50);
CREATE DOMAIN D_LCLE AS INTEGER NOT NULL;
CREATE DOMAIN D_CP AS INTEGER
	 DEFAULT 101
	 CHECK (VALUE >100 AND VALUE <1000) NOT NULL;
 
CREATE TABLE T_VILLES
(
  VL_CP	D_CP,
  VL_VILLE	D_CHAR50 NOT NULL,
CONSTRAINT PK_VILLES PRIMARY KEY (VL_CP)
);
 
 
CREATE TABLE T_CLIENTS 
(
  CL_NO	D_LCLE,
  CL_DT	DATE DEFAULT 'NOW',
  CL_ID	D_CHAR10,
  CL_INTITULE	D_CHAR50 NOT NULL,
  CL_CONTACT	D_CHAR50,
  CL_ADDR	D_CHAR50 NOT NULL,
  CL_ADDR1	D_CHAR50,
  CL_CP	D_CP NOT NULL,
  CL_TEL	D_CHAR15,
  CL_FAX	D_CHAR15,
  CL_MAIL D_CHAR50,
  CL_NIF	D_CHAR15,
  CL_RC	D_CHAR15,
  CL_STAT	D_CHAR15,
CONSTRAINT PK_CLIENTS PRIMARY KEY (CL_NO)
);
ALTER TABLE T_CLIENTS ADD CONSTRAINT FK_CLIENTS FOREIGN KEY (CL_CP) REFERENCES T_VILLES (VL_CP) ON UPDATE CASCADE ON DELETE SET DEFAULT;
 
CREATE GENERATOR GEN_CLIENTS_NO;
 
SET TERM !!
CREATE TRIGGER TRG_CLIENTS_BI FOR T_CLIENTS 
ACTIVE BEFORE INSERT POSITION 0
AS
begin
IF (NEW.CL_NO IS NULL) then NEW.CL_NO = GEN_ID(GEN_CLIENTS_NO,1);
end;
 !!
SET TERM ;!!

Bon le code est à peu près sauf faute de frappe.

J'ai besoin d'explication juste au niveau du Foreign Key.
Ici le Champs CL_CP de T_CLIENTS reference le champs VL_CP de T_VILLES.
Ici

Code :

1
2
 
ON UPDATE CASCADE ON DELETE SET DEFAULT;

Je comprends ON UPDATE et ON DELETE mais ON UPDATE ou ON DLETE sur laquelle de 2 champs : CL_CP ou VL_CP.

On a aussi le choix sur SET NULL, SET DEFAULT, CASCADE, NO ACTION.
C'est quoi la différence

Merci.

[Gillou]

l'important n'est pas ta référence , mais l'endroit référencé, donc la clé primaire de ta table ville. ici , ton code postal VL_CP

donc On update/delete se met en marche lorsque tu modifie ta clé primaire, c'est à dire VL_CP.

en effet si un client change de ville, tu veux pas que la ville suive le client.
par contre si pour une raison (exemple : tu t'est planté en entrant la valeur) une ville change de code postal, tu aimerait bien que le code postal de tes clients réagisse en conséquence.

on commence par enfoncer des portes ouvertes . Comme tu l'as compris, On update est l'action a effectuer lorsque tu change une valeur de VL_CP, et On delete est l'action effectuée lorsque tu supprime une entrée de la table VL.

Cascade ca 'transmet' l'info. c'est à dire qu'une modif de ton VL_CP entrainera la modif du CL_CP correspondant, et que la suppression du VL_CP ... ben, si t'as plus la ville, t'as plus de client dans la ville... donc suppresion des clients correspondants.

avec SET NULL les clients de la ville se retrouvent avec un CL_CP=NULL. L'intéret principal c'est lorsque on ne veux pas utiliser cascade : en effet cascade peut etre dangereux avec les effets dominos, mais surtout ca peut te bouffer des ressources rapidement ( supprime une ville dans laquelle il y a dix clients, chacun possède dix produits, chaque produit possède 10 .... ) donc dans les grosses tables la méthode est généralement on met le champ à null, et la nuit (à un moment ou la base est peu utilisée) on fait les changements nécessaires.

les deux autres.. je dois avouer que je ne suis pas sur. en gros j'ai pas envie de fouiller pour vérifier, quelqu'un me reprendra surement si je me plante ( EDIT : c confirmé , on m'a repris merci barbibulle)

SET DEFAULT, bah comme dans le cas SET NULL , mais au lieu de mettre la valeur à NULL, la valeur est mise à la valeur spécifiée comme valeur par défaut de ta colonne. Attention : pas la valeur par défaut de la colonne cible, mais la valeur par défaut de la colonne en question c'est a dire CL_CP ici. dans ton cas c la meme chose (101), mais c pas toujours le cas.

enfin, dans le comportement NO ACTION, qui est le comportement normal si tu ne spécifie rien, rien de plus que la modif que tu demande est n'est faite, et tu dois donc gérer toi meme l'intégrité référentielle : si un client a un CL-CP correspondant au VL_CP en question, tu te fait jeter parce que ton client il référence une ville et il aimerait bien que cette ville existe. en gros, si un lien sur ce VL_CP existe , alors la modification ne sera pas possible : on ne fait rien (a part un méssage d'erreur), NO ACTION.


le mieux généralement pour éviter les surprises et de rester en comportement normal : no action, et d'avoir le programme autour de ta base, ou bien des procédures stockées, faire le travail d'analyse. Ainsi tu as une protection , mais tu te laisses toujours la possibilité de faire des changements imortants si besoin, grace a un bout de code fait expres.

dernière chose, en bonus : normalement une clé primaire ne change JAMAIS donc le on update ne devrait jamais etre une question. le meilleur moyen pour ca etant généralement d'utiliser un champ supplémentaire dont la seule utilité est d'etre la clé primaire. en effet un numéro sécu peut changer ou ne pas exister , un numéro siret est encore plus variable, un code postal est stable certes, mais le défaut est la cas d'une erreur : la ville peut avoir un mauvais CP , mais peut dificilement avoir uhn mauvais ID, si cet ID est un nombre sans autre signification que de correspondre a ta ville dans ta base.


EDIT : bah voila, comme prévu j'ai été repris... merci barbibulle pour la clarification , je change mon post pour éviter qu'on y lise des c****ies.
Au fait, j'ai quand meme une demi heure d'avance sur toi et mon post est pas cours , donc va pas me dire que c parce que tu as été long a écrire

[Barbibulle]

Zut gillou a été plus rapide... j'ai fait un post trop long...

Bon c'est bien ca, Le ON UPDATE est délanché lors d'une modification du CODE POSTAL de la table VILLE. Et l'action (NO ACTION, CASCADE, SET DEFAULT, SET NULL) porte sur le code postal de ta table client.
Idem pour On Delete.


SET DEFAULT va permettre d'affecter la valeur par defaut prise par la colonne (lors de sa définition). il me semble que c'est même la valeur par defaut de la colonne référencer (CP de la table ville)...

Un autre avantage de l'intégrité référentiel c'est qu'il te sera impossible d'insérer (ou modifier) un client avec un code postal qui n'existe pas dans ta table Ville....

[Andry]

Bon si j'ai bien compris
CL_CP de la table T_CLIENTS reference VL_CP (Clé primaire) de la table T_VILLES.
Comme VL_CP ne change pas (en théorie), je vais mettre On UPDATE NO ACTION; sinon je vais modifier la Table T_VILLES et creer un champs index via un generator.
Pour ON DELETE je vais reflechir sur l'action a prendre vu que j'ai la réponse à ma question dans vos posts.

De toutes façon, je vais créer une procedure Stocké pour gerer le cas de suppression sur certains tables.

Merci les gars.

[Gillou]

pas de quoi ... il te reste plus qu'a éditer ton 1er post et rajouter la balise [Resolu] au titre du topic.