Re: RE : Foreign key - Mailing list pgsql-sql

You should keep the list copied unless you have a specific reason not to.
This allows other people to help and learn from the discussion.

On Wed, Mar 30, 2005 at 14:09:07 +0200, ASAKALAL@bouyguestelecom.fr wrote:
> Hello,
> 
> Thanks for your answers but i make the modifications, the same error returned.
> Here my script :
> 
> 
> Create table ssii ( rs_ssii VARCHAR(30),
>                                Numéro_siret integer ,
> Adresse VARCHAR(100),
> Téléphone VARCHAR(9),
> Fax VARCHAR(10),
> PRIMARY KEY (Raison sociale));
> 
> 
> Create table client ( rs_client VARCHAR(30),
>                                  Téléphone VARCHAR(10),
> Fax VARCHAR(10),
> Contact VARCHAR(30),
> PRIMARY KEY (rs_client));
> 
> 
> Create table Contrat ( numero_contrat integer,
>                                     Date_debut date,
> Date_fin date,
> rs_ssii VARCHAR(30) references ssii,
> rs_client VARCHAR(30) references client,
> code_activité VARCHAR(20) references activités,
> PRIMARY KEY (numero_contrat, rs_ssii, rs_client, code_activité)) ;
> 
> 
> 
> Create table activités (code_activité VARCHAR(20), 
>                                    Libellé text,
> Imputation VARCHAR(6),
> Nature VARCHAR(20),
> Commentaire text,
> Durée decimal(5,3),
> PRIMARY KEY ( Code_activité)) ;
> 
> 
> 
> Create table Salariés (Nom_salarié VARCHAR(20), 
>                                    Prénom VARCHAR(20),
> Fonction VARCHAR(50),
> Service VARCHAR(50),
> Adresse VARCHAR(100),
> Numero_SS integer,
> Matricule VARCHAR(6),
> rs_ssii VARCHAR(30) references ssii,
> PRIMARY KEY ( Nom_salarié, rs_ssii)) ;
> 
> 
> Create table Compteur (id integer, 
>                                    Heures_travaillées decimal(6,2),
> Cp_acquis decimal(6,2),
> Cp_pris decimal(6,2),
> RTT_acquis decimal(6,2),
> RTT_pris decimal(6,2),
> Nom_salarié VARCHAR(20) references salariés,

Unless Nom_salarié is unique in the salariés table (and you add a UNIQUE
declaration for it to that table), you won't be able to do this.

> PRIMARY KEY ( Nom_salarié, Id)) ;
> 
> NOTICE : create table/primary key will create implicit index "compteur_pkey for table" compteur.
> ERROR : < number of referencing and referenced colums for foreign key disagree>.
> 
> So i add this ligne for referencing the two primary key of table salariés 
> 
> Create table Compteur (id integer, 
>                                    Heures_travaillées decimal(6,2),
> Cp_acquis decimal(6,2),
> Cp_pris decimal(6,2),
> RTT_acquis decimal(6,2),
> RTT_pris decimal(6,2),
> Nom_salarié VARCHAR(20) references salariés,
> rs_ssii VARCHAR(30) references ssii,
> PRIMARY KEY ( Nom_salarié,rs_ssii,Id)) ;
> 
> The same error is returned.

This approach will work, but you aren't doing it correctly. Instead of two
column references you want to make a foreign key referece such as:

FOREIGN KEY (Nom_salarié, rs_ssii) REFERENCES salariés,

> 
> alain SAKALALA
> DOR/OCR Support N1 SMS et VOIX
> Mailto:asakalal@bouyguestelecom.fr
> 
> 
> 
> 
> 
> 
> 
> 
> -----Message d'origine-----
> De : Bruno Wolff III [mailto:bruno@wolff.to] 
> Envoyé : dimanche 27 mars 2005 18:33
> À : SAKALALA, Alain
> Cc : pgsql-sql@postgresql.org
> Objet : Re: Foreign key
> 
> On Fri, Mar 25, 2005 at 16:31:16 +0100,
>   ASAKALAL@bouyguestelecom.fr wrote:
> > 
> > When i add table with foreign key in my database, this error return : <
> > number of referencing and referenced colums for foreign key disagree>.
> > 
> > How resolve this problem ?
> 
> Besides what Mike said, one other thing to remember is that if you don't
> specify columns in the referenced table, the primary key of that table
> is used, NOT columns with names matching those of the referencing table.
> 
> In cases like this it have helped if you had copied and pasted an example
> displaying the problem in addition to the error message.