pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Re: RE : Foreign key 2+ messages / 2 participants [nested] [flat]
* Re: RE : Foreign key @ 2005-03-30 16:31 Michael Fuhr <mike@fuhr.org> 0 siblings, 0 replies; 2+ messages in thread From: Michael Fuhr @ 2005-03-30 16:31 UTC (permalink / raw) To: ASAKALAL@bouyguestelecom.fr; +Cc: pgsql-sql On Wed, Mar 30, 2005 at 02:09:05PM +0200, ASAKALAL@bouyguestelecom.fr wrote: > > Thanks for your answers but i make the modifications, the same error returned. > Here my script : > ... > 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)) ; The salariés table has a primary key with two columns. > 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 foreign key reference to salariés has only one column but the primary key for salariés has two columns, hence the error. It looks like you should be doing this: Nom_salarié VARCHAR(20), rs_ssii VARCHAR(30), FOREIGN KEY (Nom_salarié, rs_ssii) REFERENCES salariés, -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: RE : Foreign key @ 2005-03-30 17:26 Bruno Wolff III <bruno@wolff.to> 0 siblings, 0 replies; 2+ messages in thread From: Bruno Wolff III @ 2005-03-30 17:26 UTC (permalink / raw) To: ASAKALAL@bouyguestelecom.fr; +Cc: 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-03-30 17:26 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-03-30 16:31 Re: RE : Foreign key Michael Fuhr <mike@fuhr.org> 2005-03-30 17:26 Re: RE : Foreign key Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox