Discussion :

[laurentSc]

Bonjour,

j'ai une table dont une colonne appartient à la clé étrangère (celle-ci porte sur 2 colonnes) et à laquelle j'ai aussi attribué une clé étrangère :
table `license`:

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
DROP TABLE IF EXISTS `license`;
CREATE TABLE `license` (
  `id_SESA` int(6) NOT NULL,
  `application_key` smallint(2) NOT NULL,
  `currently_active` tinyint(1) DEFAULT NULL,
  `activate_date` date DEFAULT NULL,
  `deactivate_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
--
-- Index pour la table `license`
--
ALTER TABLE `license`
  ADD PRIMARY KEY (`id_SESA`,`application_key`) USING BTREE,
  ADD KEY `fk_application` (`application_key`);
 
--
-- Contraintes pour la table `license`
--
ALTER TABLE `license`
  ADD CONSTRAINT `fk_application` FOREIGN KEY (`application_key`) REFERENCES `application` (`application_key`),
  ADD CONSTRAINT `fk_user` FOREIGN KEY (`id_SESA`) REFERENCES `user` (`sesa`);

un essai dans PhpMyadmin d'insertion dans cette table semble indiquer que c'est autorisé (clé primaire + clé étrangère). Me confirmez-vous que oui ?

Et d'ailleurs, ceci étant le résultat d'un export, comment se fait-il qu'on trouve 2 fois la déclaration de `fk_application` ?

[al1_24]

Dans ton cas, la colonne application_key n'est pas à la fois clé primaire et clé étrangère ; elle est constituante de la clé primaire, qui comporte deux colonnes.
Mais même si elle était l'unique constituant de la clé primaire, cela serait aussi autorisé.
C'est d'ailleurs ce que l'on retrouve dans les cas de formalisation d'héritage.

Il n'y a pas de double déclaration de fk_application. Il y a d'une part un index et de l'autre une contrainte d'intégrité référentielle.
Il faudrait d'ailleurs se poser la question si l'index est réellement utile en fonction des requêtes qui seront effectuées sur cette table.

[laurentSc]

Merci pour la réponse.

Oui, l'index sur la colonne application_key ne servait à rien (je me demande d'où il vient) et je l'ai supprimé.

En fait, j'ai 2 colonnes constituantes de la clé primaire : id_SESA et application_key et elles sont déclarées toutes les 2 comme clé étrangère ! ( id_SESA vers la table user et application_key vers la table application )

[StringBuilder]

La réponse au titre est OUI, c'est ce qu'on fait dans le cadre de l'héritage.

Par exemple une table "tiers" :

Code sql :

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

1
2
3
4
5
6
 
create table tiers
(
   id int primary key identity,
   nom char(30) not null
);

Une table "client" qui étend la table "tiers" :

Code sql :

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

1
2
3
4
5
6
 
create table client
(
   id int primary key references tiers(id),
   prenom char(30) not null
);

Un client X est de facto un tiers X : on utilise donc le même identifiant, et on indique qu'un client est forcément lié à un tiers.

Ensuite, ton cas précis (clé primaire composée de deux clés étrangères) est typiquement le cas d'une table de jointure.
Par exemple :

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
 
create table client
(
   id int primary key identity,
   nom char(30) not null
);
 
create table voiture
(
   id int primary key identity,
   immatriculation char(7) not null
);
 
create table location
(
   id_client int not null references client(id),
   id_voiture int not null references voiture(id),
   debut date not null,
   fin date not null,
   primary key (id_client, id_voiture)
);

=> Une location est identifiée par un client et une voiture. Avec une date de début et de fin... attention cependant, dans ce cas un client ne peu louer qu'une seule fois chaque voiture, c'est pas forcément ce qu'on veut

PS : dans tous les cas, c'est une pratique à éviter cependant. Il faut toujours privilégier une clé unitaire et dénuée de sens. Donc créer une colonne "id" clé primaire, et mettre une contrainte d'unicité sur les deux clé étrangères. Ce sera plus performant, surtout si tu insères beaucoup de données et que tu as d'autres index sur ta table.

[laurentSc]

PS : dans tous les cas, c'est une pratique à éviter cependant. Il faut toujours privilégier une clé unitaire et dénuée de sens. Donc créer une colonne "id" clé primaire, et mettre une contrainte d'unicité sur les deux clé étrangères. Ce sera plus performant, surtout si tu insères beaucoup de données et que tu as d'autres index sur ta table.

Est-ce que ta remarque concerne aussi ma table et que je devrais aussi ajouter une colonne id dénuée de sens et qui servirait de clé primaire ?

Sinon, mettre une clé d'unicité sur les 2 colonnes-clés étrangères, ça me surprend, comme un utilisateur (identifié par la colonne id_SESA) peut posséder une licence de plusieurs applications donc apparaître dans plusieurs enregistrements, et pareil pour la colonne application_key, vu que plusieurs utlisateurs peuvent détenir une licence pour la même application et que donc la même valeur pour la colonne application_key apparaîtra aussi dans plusieurs enregistrements...

[aieeeuuuuu]

Bonjour

PS : dans tous les cas, c'est une pratique à éviter cependant. Il faut toujours privilégier une clé unitaire et dénuée de sens. Donc créer une colonne "id" clé primaire, et mettre une contrainte d'unicité sur les deux clé étrangères. Ce sera plus performant, surtout si tu insères beaucoup de données et que tu as d'autres index sur ta table.

Même si la remarque est vraie d'un point de vue général, en l’occurrence, je mettrais un bémol.
Sur une telle table (associative sur seulement deux FK), laisser la clef primaire sur le couple de clefs étrangères permet d'avoir un index cluster (et donc forcément couvrant) pour la moitié (voire bien plus selon l'usage) des requêtes, et il suffit de placer un deuxième index sur la deuxième clef pour couvrir tous les cas, et éliminer les recherches de clef, souvent couteuses.

[SQLpro]

C'est systématique dès que l'on a une entité fiable :
1) le cas de l'héritage
2) le cas de l'association identifiante

Voici un extrait de mon nouveau livre à paraitre sur le langage SQL (passage du MCD au MLD dans le cas de l'héritage) :


A +