X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DB4B553479 for ; Wed, 30 Mar 2005 18:10:15 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 62076-06 for ; Wed, 30 Mar 2005 17:10:03 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 51D5E53474 for ; Wed, 30 Mar 2005 18:10:02 +0100 (BST) Received: (qmail 17703 invoked by uid 500); 30 Mar 2005 17:26:39 -0000 Date: Wed, 30 Mar 2005 11:26:39 -0600 From: Bruno Wolff III To: ASAKALAL@bouyguestelecom.fr Cc: pgsql-sql@postgresql.org Subject: Re: RE : Foreign key Message-ID: <20050330172639.GA17497@wolff.to> Mail-Followup-To: Bruno Wolff III , ASAKALAL@bouyguestelecom.fr, pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/392 X-Sequence-Number: 20871 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.