Discussion :

[moimp]

Bonjour,

J'ai les définitions de 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
CREATE TABLE IF NOT EXISTS `dat_users` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_subscriber` int(10) UNSIGNED NOT NULL,
  `login` varchar(12) NOT NULL,
  `hash` varchar(255) DEFAULT NULL,
  `user_type` tinyint(1) NOT NULL DEFAULT '1',
  `rights` varchar(50) NOT NULL DEFAULT '0-2,1-2,2-2,3-2,4-2,5-2,6-2,7-2,8-2,9-2',
  `token` varchar(255) DEFAULT NULL,
  `checked` tinyint(1) DEFAULT NULL,
  `banned` tinyint(1) DEFAULT NULL,
  `is_writer` tinyint(1) UNSIGNED DEFAULT NULL,
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`id_subscriber`,`login`),
  KEY `user_type` (`user_type`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
 
ALTER TABLE `dat_users`
  ADD CONSTRAINT `dat_users_ibfk_1` FOREIGN KEY (`id_subscriber`) REFERENCES `dat_subscribers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 
CREATE TABLE IF NOT EXISTS `lst_salesagents` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_subscriber` int(10) UNSIGNED NOT NULL,
  `agentname` varchar(20) DEFAULT NULL,
  `user_type` int(1) UNSIGNED DEFAULT NULL,
  `rank` int(10) UNSIGNED DEFAULT NULL,
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `id_subscriber` (`id_subscriber`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

Lorsque j'essaie de créer une contrainte de clef étrangère sur la deuxième table comme ceci ALTER TABLE `lst_salesagents` ADD FOREIGN KEY (`user_type`) REFERENCES `dat_users`(`user_type`) ON DELETE SET NULL ON UPDATE CASCADE;, j'obtiens une erreur

#1215 - Impossible d'ajouter des contraintes d'index externe

J'ai regardé la doc et je ne vois pas ce qui ne va pas.

[Artemus24]

Salut moimp.

Pour créer une contrainte de clef étrangère, il faut :

1) que la table mère doit exister aupréalable. Cela signifie que les valeurs servant de clef doivent exister.

2) l'ordre de création des tables a de l'importance. Il faut créer la table mère avant la table fille.

3) que la colonne servant de référence soit une "primary key". Cela implique que le NULL est interdit.

4) que la déclarative de la colonne de la table mère, servant de référence soit identique à celle de la table fille, servant de clef étrangère.

5) le NULL dans la clef étrangère n'est pas admins.

6) on ne peut faire référence à une valeur dans la clef étrangère de la table fille que si cette valeur existe déjà dans la table mère.

Code :

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

1
2
ALTER TABLE `dat_users`
ADD CONSTRAINT `dat_users_ibfk_1` FOREIGN KEY (`id_subscriber`) REFERENCES `dat_subscribers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Je ne vois pas la table `dat_subscribers`.
Si elle est créé après la table `dat_users`, c'est normal que la création de la contrainte ne fonctionne pas.

@+

[moimp]

Bonjour et merci.
A la lecture de la documentation et de votre réponse, plutôt plus claire, je constate que ma base de données est mal conçue.
L'application est une mini CRM (customer relationship management).
Cette base de données sert à alimenter une application prévue pour être utilisée par plusieurs abonnés-souscripteurs (table dat_subscribers) sans mélange de données. La table dat_users est mal nommée et mal conçue. Il ne s'agit pas d'une table utilisateurs mais d'une table catégorie d'utilisateurs, chaque catégorie confère des droits aux utilisateurs (1=webmaster, 2=administrateur qui donne les droits, 3=lecture/écriture, 4=lecture uniquement).
Actuellement les utilisateurs se connectent non pas avec un pseudo qui leur est propre mais par leur catégorie ce qui signifie que plusieurs utilisateurs d'un même souscripteur peuvent se connecter avec la même catégorie et donc le même mot de passe ce qui peut peut-être entrainer des confusions ou des conflits. Là, avant de continuer, j'ai besoin de l'éclairage d'un spécialiste.

[moimp]

J'ai repris la structure de ma base de données en séparant les utilisateurs et les catégories d'utilisateurs. C'est beaucoup mieux et surtout beaucoup plus clair.

[escartefigue]

bonjour,

Salut moimp.

Pour créer une contrainte de clef étrangère, il faut :

1) que la table mère doit exister aupréalable. Cela signifie que les valeurs servant de clef doivent exister.

2) l'ordre de création des tables a de l'importance. Il faut créer la table mère avant la table fille.

3) que la colonne servant de référence soit une "primary key". Cela implique que le NULL est interdit.

4) que la déclarative de la colonne de la table mère, servant de référence soit identique à celle de la table fille, servant de clef étrangère.

5) le NULL dans la clef étrangère n'est pas admins.

6) on ne peut faire référence à une valeur dans la clef étrangère de la table fille que si cette valeur existe déjà dans la table mère.

Les points 1 et 6 sont identiques, la formulation de la 6 est préférable car c'est bien la valeur référencée qui doit être présente dans la table "mère" et non pas la table

Le point 3 est faux : le standard SQL impose la présence d'une contrainte unique sur la colonne référencée, pas forcément une PK.
De plus, INNODB déroge à la règle, car il accepte des contraintes sur des colonnes non uniques (mais obligatoirement indexées)

Pour le point 4, dit autrement, les colonnes doivent être de même type et de même longueur

Et il faut ajouter deux points :
- les colonnes référençante et référencée doivent être dans des tables ayant le même moteur
- la colonne référencée ne peut pas être une colonne virtuelle calculée

cf. la doc ici
https://dev.mysql.com/doc/refman/8.0...eign-keys.html
https://dev.mysql.com/doc/refman/8.0...eign-keys.html

[moimp]

Merci pour ces précisions.