clé primaire/clé étrangère [Résolu]

[100%fluor]

Bonjour,

j'ai un petit problème fondamentale de SQL:

d'abord les tables:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE t1(
np int REFERENCES autre_table (champs),
nt int REFERENCES autre_table (champs),
na SERIAL UNIQUE NOT NULL,
PRIMARY KEY (na,np,nt)
) WITH OIDS;
 
CREATE TABLE t3(
nq SERIAL UNIQUE NOT NULL,
np int NOT NULL, 
nt int NOT NULL,
na int NOT NULL,
FOREIGN KEY (na,np,nt) REFERENCES t1 (na,np,nt)[/INDENT]) WITH OIDS;

les tables ont ete créées sans problème

valeurs de t1 (la clé primaire est bien 1,3,1)

Code :

1
2
3
4
 
 np |  nt | na 
----+----+---
   3 |  1 | 1

losrque que je veux remplir t2 avec:

Code :

1
2
 
INSERT INTO t2 VALUES (DEFAULT,3,1,1);

je recois le message d'erreur suivant:

Code :

1
2
3
 
ERREUR:  Une instruction INSERT ou UPDATE sur la TABLE t1 viole la contrainte de clé étrangère t1_fkey
DÉTAIL : La clé (na,np,nt)=(1,3,1) n est pas présente dans la TABLE t1

je ne comprends pas ou se trouve le probleme:
1- une clé etrangère fait bien référence à une clé primaire (multiple)
2- pour ne pas faire référence à une clé primaire elle même clé étrangère, il y a dans cette clé primaire multiple (de t1) le champs "na" qui n'est pas une référence, qui est unique et non nulle (un index est créé pour na et un autre pour (na,np,nt) ).

Le SGBG (ici postgre) devrait pouvoir la retrouver (?)
comment récupérer (np,nt) sans passer par les tables d'origines? (c'est le but de la manip)

Merci à la communauté,

[soazig]

Coucou,
Je pense que ton problème vient de l'ordre de tes colonnes, il ne faut presque jamais faire un insert into Table1 Values sans spécifier les colonnes , parce que si tu rajoute un champ dans la table ton insert foire.
Et là l'ordre de tes colonnes ne semble pas être celui que tu imagines.
Donc je propose simplement de spécifier les colonnes

Code :

INSERT INTO t2 (np,nt,na) VALUES (3,1,1);

Tu t'es mélangé les crayons en voulant renommer tes tables tu décris la table t3 et tu insère dans t2 . Par ailleurs ton message d'erreur indique

ERREUR: Une instruction INSERT ou UPDATE sur la TABLE t1

Ne serait-ce pas plutôt sur la table t2?

a+
Soazig

[100%fluor]

tu as raison pour le message d'erreur il s'agit bien de:

Code :

1
2
ERREUR:  Une instruction INSERT ou UPDATE sur la TABLE t2 viole la contrainte de clé étrangère t1_fkey
DÉTAIL : La clé (na,np,nt)=(1,3,1) n est pas présente dans la TABLE t1

même en spécifiant le nom des champs avec:

Code :

INSERT INTO t2 (nq,np,nt,na) VALUES (DEFAULT,3,1,1);

j'obtiens le même message d'erreur.

[100%fluor]

Bon j'ai repris le probleme en créant mes tables SANS OIDS et ca fonctionne (?
c'est super ca enleve mon probleme) Mais j'aimerai bien savoir pourquoi ca fonctionne et surtout pourquoi ca ne fonctionnait pas ? quel est le probleme avec l'OIDS ?

Je pense que c'est une question qui pourrait intéresser pa mal de personnes.(non ?)

Merci à tous,

[chrifo]

Bonjour,
Comme ça m'intriguait j'ai fait le test complet (en postgres 8.2) :

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
49
50
51
52
53
54
55
 
tests=> CREATE TABLE t01(
tests(> chp_t01 int,
tests(> PRIMARY KEY (chp_t01)
tests(> ) WITH OIDS;
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "t01_pkey" FOR TABLE "t01"
CREATE TABLE
tests=> INSERT INTO t01 VALUES(3);
INSERT 27496 1
tests=>
tests=> CREATE TABLE t02(
tests(> chp_t02 int,
tests(> PRIMARY KEY (chp_t02)
tests(> ) WITH OIDS;
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "t02_pkey" FOR TABLE "t02"
CREATE TABLE
tests=> INSERT INTO t02 VALUES(1);
INSERT 27501 1
tests=>
tests=> CREATE TABLE t1(
tests(> np int REFERENCES t01(chp_t01),
tests(> nt int REFERENCES t02(chp_t02),
tests(> na SERIAL UNIQUE NOT NULL,
tests(> PRIMARY KEY (na,np,nt)
tests(> ) WITH OIDS;
NOTICE:  CREATE TABLE will CREATE implicit sequence "t1_na_seq" FOR serial COLUMN "t1.na"
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "t1_pkey" FOR TABLE "t1"
NOTICE:  CREATE TABLE / UNIQUE will CREATE implicit INDEX "t1_na_key" FOR TABLE "t1"
CREATE TABLE
tests=> INSERT INTO t1 VALUES (3,1,1);
INSERT 27521 1
tests=> SELECT * FROM t1;
 np | nt | na
----+----+----
  3 |  1 |  1
(1 row)
 
tests=>
tests=> CREATE TABLE t2(
tests(> nq SERIAL UNIQUE NOT NULL,
tests(> np int NOT NULL,
tests(> nt int NOT NULL,
tests(> na int NOT NULL,
tests(> FOREIGN KEY (na,np,nt) REFERENCES t1 (na,np,nt)) WITH OIDS;
NOTICE:  CREATE TABLE will CREATE implicit sequence "t2_nq_seq" FOR serial COLUMN "t2.nq"
NOTICE:  CREATE TABLE / UNIQUE will CREATE implicit INDEX "t2_nq_key" FOR TABLE "t2"
CREATE TABLE
tests=>
tests=> INSERT INTO t2 VALUES (DEFAULT,3,1,1);
INSERT 27534 1
tests=> SELECT * FROM t2;
 nq | np | nt | na
----+----+----+----
  1 |  3 |  1 |  1
(1 row)

Et ça fonctionne ... quelle est votre version de postgres ?
L'insertion dans t1 est-il bien "commité" ?

PS : Il s'agit d'un pb postgres, vous auriez eu plus de réponses dans le forum postgres

[100%fluor]

désolé au debut je pensais qu'il s'agissait d'un probleme de SQL et pas de posgre. Je me suis trompé.

ton exemple fonctionne chez moi mais pas sur mes tables (???)
tu veux dire quoi par "commité" ?
(Merci)

[chrifo]

"Commité" est un abus de langage, le "commit" est lié à la notion de transaction : http://www.postgresql.org/docs/8.2/i...nsactions.html

En gros : es-tu certain que la valeur 3,1,1 est bien présente dans la base, ou est elle seulement en cours d'insertion dans une autre session ?
C'est une piste, si elle ne mène à rien essaie de poser la question dans le forum postgres, des forumeurs plus expérimentés que moi sur ce SGBD te donneront d'autres pistes

La question à te poser est : quelle est la différence entre mon exemple qui fonctionne et le tien qui ne fonctionne pas ?

[100%fluor]

Merci pour le lien. je n'avais pas lu la doc sur les transactions et c'est très intéressant.

A propos du champs de valeurs (3,1,1), il existe bien (et je l'ai choisi pour ca d'ailleur). C'est bien une clé primaire : je le selectionne sans probleme sur t1, et la description de la table t1 obtenue par la commande

Code :

\d t1

me confirme que c'est bie la clé primaire.

Mis à part le nom des champs, ma table est bien exactement la meme que t1 (selon la description sur postgre faite par les commandes):

Code :

\d t1

Code :

\d ma_table

A propos de TRANSACTION/COMMIT:
Sachant que je remplie mes tables une par une (donc les données d'entrées de références sont bien présente avant le remplissage de la nouvelle table), il ne pouvait pas s'agir d'un probleme de TRANSACTION/COMMIT.
Mais:
il s'agit peut être de la maniere dont je cree mes tables:
j'ecris le code pour creer toutes les tables de ma base dans un fichier texte. Puis je fais un copier/coller de la totalité dans Posgre. Toutes les tables sont crées mais je me suis apercu que certain champs "collais" TRANSACTION automatiquement (???).
(NB: il ne s'agit pas d'un probleme d'editeur de texte (emacs) ni de codage!)

Est ce le fait de creer les tables a la volée ? (elle sont toutes codées dans le bon ordre et elles sont bien toutes créées)

bref, j'ai effacé toutes mes tables et je recommence à zéro (50.10^3 entrées à la poubelle)

[GrandFather]

Bonjour,

Envoyé par 100%fluor

Est ce le fait de creer les tables a la volée ? (elle sont toutes codées dans le bon ordre et elles sont bien toutes créées)

Cela pourrait être effectivement lié au fait que la création des tables et leur alimentation s'effectue dans une seule et même transaction, du fait de la méthode que tu emploies pour exécuter les requêtes SQL. Il y a peut-être un souci de précédence entre la création de la clé primaire et la vérification de la contrainte.

Cela peut être réglé en modifiant les contraintes de clé étrangères en leur ajoutant les mentions DEFERRABLE et INITIALLY DEFERRED (qui décident du moment exact où les contraintes sont vérifiées, en l'occurrence à la fin de la transaction alors que par défaut c'est après chaque instruction, ce qui pose problème ici). Maintenant, il est peut-être plus simple de faire directement exécuter sous le shell ton fichier contenant les commandes SQL (plutôt que de faire un copier/coller), avec une instruction telle que :

Code :

psql < le_nom_de_ton_fichier

Avec cette méthode, il n'y pas de transaction "globale" chaque instruction faisant l'objet d'un auto-COMMIT.

[100%fluor]

effectivement il ya avait bien un probleme de TRANSACTION du au copier/coller:
j'ai (re)-créé toutes les tables à la main et c'était.... presque parfait.

en effet une décalaration de la clé primaire de ce type:

Code :

1
2
3
4
 
CREATE TABLE t01 (
id SERIAL PRIMARY KEY,
etc...

me renvoie le même message d'erreur (clé non existante...)

Les clés ont dut être déclarées "proprement", c.a.d :

Code :

1
2
3
4
5
 
CREATE TABLE t01 (
id SERIAL,
PRIMARY KEY (id),
etc...

Conclusion:
1- tout faire dans les règles strictes: fini les copier/coller et éviter tous les racourcis
2-

Un grand merci a tous,