Date: Sun, 27 Mar 2005 00:47:20 -0700
From: Michael Fuhr <mike@fuhr.org>
To: ASAKALAL@bouyguestelecom.fr
Cc: pgsql-sql@postgresql.org
Subject: Re: Foreign key
Message-ID: <20050327074720.GB46207@winnie.fuhr.org>
References: 
 <DB7F9CBEADC6D4118F5600508B442F0308611FA4@bt1sqtbe.bpa.bouyguestelecom.fr>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <DB7F9CBEADC6D4118F5600508B442F0308611FA4@bt1sqtbe.bpa.bouyguestelecom.fr>

On Fri, Mar 25, 2005 at 04:31:16PM +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>.

Apparently the referencing key (the foreign key specification) has
a different number of columns than the referenced key (the primary
key or other unique key in the referenced table).  Here's an example
that illustrates the problem:

  CREATE TABLE foo (
      pk1  integer NOT NULL,
      pk2  integer NOT NULL,
      PRIMARY KEY (pk1, pk2)  -- 2-column primary key
  );
  
  CREATE TABLE bar (
      fk  integer NOT NULL REFERENCES foo  -- 1-column foreign key
  );
  
  ERROR:  number of referencing and referenced columns for foreign key disagree

In the above example we need a 2-column foreign key:

  CREATE TABLE bar (
      fk1  integer NOT NULL,
      fk2  integer NOT NULL,
      FOREIGN KEY (fk1, fk2) REFERENCES foo
  );

Here's another example that references a 1-column unique key that
isn't a primary key:

  CREATE TABLE foo (
      pk1  integer NOT NULL,
      pk2  integer NOT NULL,
      x    integer NOT NULL,
      PRIMARY KEY (pk1, pk2),
      UNIQUE (x)
  );
  
  CREATE TABLE bar (
      fk  integer NOT NULL REFERENCES foo (x)
  );

If these examples don't help, then please post the table definitions
you're working with and explain what you'd like to do.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/