Discussion :

[StringBuilder]

Bonjour,

Je viens vers vous avec une question qui est certainement redondante.

Comment modéliser une clé étrangère qui pointe sur une table qui est elle-même liée à la table courante ?

Par exemple, si on prend les fichiers de l'INSEE concernant les communes et les départements.

Dans la table des communes, on retrouve une clé étrangère vers le département dans lequel se situe la commune.

Mais en revanche, le département possède un lien vers le chef lieu, qui est lui-même une commune.

Donc on aurait :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
=sql
create table departement
(
   id int not null primary key,
   nom varchar(50) not null,
   chef_lieu int not null references commune(id)
);

create table commune
(
   id int not null primary key,
   nom varchar(50) not null,
   departement int not null references departement(id)
);

Outre le fait qu'on ne peut pas exécuter le script tel quel, une fois les clés étrangères correctement positionnées, il est impossible d'insérer la moindre ligne dans les tables : si on crée un département, on doit renseigner un chef lieu, qui n'existe pas encore, et si on veut créer une commune, on doit renseigner un département qui n'existe pas encore.

Existe-t-il dont un moyen ?
- soit de dénormaliser (ou renormaliser) la chose, pour avoir des contraintes classiques
- soit une astuce technique (sous SQL Server dans mon cas) qui permette de vivre avec un tel modèle des données, sans devoir désactiver les clés étrangères à chaque manipulation des données

Le tout, évidement, tout en conservant l'intérêt d'un SGBD-R, à savoir :
- qu'on ne puisse pas créer une commune qui ne soit pas dans un département
- qu'on ne puisse pas créer un département qui n'ait pas de chef-lieu

[fsmrel]

Bonsoir Sylvain,

Si on crée un département, on doit renseigner un chef-lieu, qui n'existe pas encore, et si on veut créer une commune, on doit renseigner un département qui n'existe pas encore.

SQL Server ne permet pas de mettre en oeuvre un tel cycle, car il n’autorise pas les contraintes différées pour les contraintes d’intégrité référentielle (contrainte d’état : INITIALLY DEFERRED, voyez par exemple la norme SQL, ou Oracle).

On peut au moins rompre le cycle (quoi qu’en pensent les coureurs du Tour de France en cours...)

Exemple :

On définit une table CHEF_LIEU, dotée de deux clés candidates {DepartementId} et {CommuneId} :




Mais il y a un problème : rien n’empêcherait de définir comme chef-lieu du département d1 une commune appartenant au département d2...


Pour pallier, on peut programmer un trigger, mais on peut préférer en passer par l’identification relative :





Code SQL correspondant :

TABLE DEPARTEMENT

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE DEPARTEMENT 
(
   DepartementId        INT NOT NULL,
   DepartementNumero    VARCHAR(3) NOT NULL,
   DepartementNom       VARCHAR(64) NOT NULL,
   CONSTRAINT DEPARTEMENT_PK PRIMARY KEY (DepartementId),
   CONSTRAINT DEPARTEMENT_NUMERO_AK UNIQUE (DepartementNumero),
   CONSTRAINT DEPARTEMENT_NOM_AK UNIQUE (DepartementNom)
) ;

TABLE COMMUNE

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE COMMUNE 
(
   DepartementId        INT NOT NULL,
   CommuneId            INT NOT NULL,
   CommuneInsee         CHAR(5) NOT NULL,
   CommuneNom           VARCHAR(64) NOT NULL,
   CONSTRAINT COMMUNE_PK PRIMARY KEY (DepartementId, CommuneId),
   CONSTRAINT COMMUNE_INSEE_AK UNIQUE (CommuneInsee),
   CONSTRAINT COMMUNE_DEPARTEMENT_FK FOREIGN KEY (DepartementId)
      REFERENCES DEPARTEMENT
) ;

TABLE CHEF_LIEU

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE CHEF_LIEU 
(
   DepartementId        INT NOT NULL,
   CommuneId            INT NOT NULL,
   CONSTRAINT CHEF_LIEU_PK PRIMARY KEY (DepartementId, CommuneId),
   CONSTRAINT CHEF_LIEU_DEPARTEMENT_FK FOREIGN KEY (DepartementId)
      REFERENCES DEPARTEMENT,
   CONSTRAINT CHEF_LIEU_COMMUNE_FK FOREIGN KEY (DepartementId, CommuneId)
      REFERENCES COMMUNE
) ;

Un jeu d’essai :

Code :

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

1
2
3
4
5
6
7
8
9
10
INSERT INTO DEPARTEMENT (DepartementId, DepartementNumero, DepartementNom) VALUES (1, '92', 'Hauts-de-Seine') ;
INSERT INTO DEPARTEMENT (DepartementId, DepartementNumero, DepartementNom) VALUES (2, '2A', 'Corse-du-Sud') ;

INSERT INTO COMMUNE (DepartementId, CommuneId, CommuneInsee, CommuneNom) VALUES (1, 1, '92046', 'Malakoff') ;
INSERT INTO COMMUNE (DepartementId, CommuneId, CommuneInsee, CommuneNom) VALUES (1, 2, '92050', 'Nanterre') ;
INSERT INTO COMMUNE (DepartementId, CommuneId, CommuneInsee, CommuneNom) VALUES (2, 1, '2A004', 'Ajaccio') ;
INSERT INTO COMMUNE (DepartementId, CommuneId, CommuneInsee, CommuneNom) VALUES (2, 2, '2A272', 'Sartène') ;

INSERT INTO CHEF_LIEU (DepartementId, CommuneId) VALUES (1, 2) ;
INSERT INTO CHEF_LIEU (DepartementId, CommuneId) VALUES (2, 1) ;

Et ça tourne (sans erreur d'affectation possible) :

Code SQL :

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

1
2
3
SELECT DepartementNom, CommuneNom AS 'Chef-lieu' 
FROM   DEPARTEMENT AS x JOIN COMMUNE AS y ON x.DepartementId = y.DepartementId
                        JOIN CHEF_LIEU AS z ON y.DepartementId = z.DepartementId AND y.CommuneId = z.CommuneId ;

=>

Code :

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

1
2
3
4
Département        Chef-lieu
--------------     ---------
Hauts-de-Seine     Nanterre
Corse-du-Sud       Ajaccio

Mais on n’a pas la garantie qu’un département ait automatiquement un chef-lieu...

[StringBuilder]

Du coup (en raison de la dernière ligne) n'est-ce pas plus simple de :
- laisser le chef lieu nullable
- ajouter une contrainte check (avec une fonction scalaire) qui vérifie que le département de rattachement du chef lieu est bien égal à l'id du département

Code sql :

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
 
create table departement
(
   id int not null primary key identity,
   code char(2) not null unique,
   nom varchar(50) not null,
   commune_id int null
);
go
 
create table commune
(
   id int not null primary key,
   code char(5) not null unique,
   nom varchar(50) not null,
   departement_id int not null references departement(id)
);
go
 
alter table departement
add constraint fk_departement_cheflieu
foreign key (commune_id)
references commune(id);
go
 
create function DepartementCommune
(
	@id_commune int
)
returns int
as
begin
	return (select commune.departement_id from commune where commune.id = @id_commune);
end;
go
 
alter table departement
with check add constraint ck_departement_cheflieu check (dbo.DepartementCommune(commune_id) = id or commune_id is null);
go

Car du coup, le modèle reste plus lisible, non ?

En revanche, on garde le problème du département sans chef lieu...
Bon, tant pis.

[fsmrel]

En revanche, on garde le problème du département sans chef lieu...

Le jour où les SGBD suivront la théorie relationnelle, ça sera bien. Dans le problème qui nous intéresse, il faudrait que l’on puisse concaténer les inserts dans un même paquet, c'est-à-dire les séparer par une virgule, le point-virgule marquant la fin du paquet. Tant que le point-virgule n’est pas détecté, pas de contrôle, mais une fois détecté alors le SGBD vérifie dans tous les sens.

Pour reprendre votre exemple initial, en procédant ainsi :

Code SQL :

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

1
2
3
Insert into departement (id, nom, chef-lieu) values (1, 'Hauts-de-Seine', 17),

Insert into commune (id, nom, departement) values (17, 'Nanterre', 1) ;

Le contrôle de l’intégrité référentielle n’est alors déclenché qu’après l’insert de la commune. Quant à la contrainte d’état « INITIALLY DEFERRED » de la norme SQL c’est un pis-aller, car entre les deux inserts il peut s’en passer des choses, mais c'est mieux que rien...