Re: RE : Foreign key

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(at)bouyguestelecom(dot)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(at)bouyguestelecom(dot)fr
>
>
>
>
>
>
>
>
> -----Message d'origine-----
> De : Bruno Wolff III [mailto:bruno(at)wolff(dot)to]
> Envoyé : dimanche 27 mars 2005 18:33
> À : SAKALALA, Alain
> Cc : pgsql-sql(at)postgresql(dot)org
> Objet : Re: Foreign key
>
> On Fri, Mar 25, 2005 at 16:31:16 +0100,
> ASAKALAL(at)bouyguestelecom(dot)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.