Message-ID: <42BEC2AA.9030404@email.it>
Date: Sun, 26 Jun 2005 16:58:50 +0200
From: davide <site.webmaster@email.it>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: multiple PK with a non UNIQUE field
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Sorry, I 'm a beginner of postgres and I found a problem when I was 
passed from MySQL:
I have to create a referential constraint table APPALTO from a table 
OPERE that as a composed PK(cod_op,cod_com) where cod_op ISN'T unique.
in MySQL:
CREATE TABLE opere (
    cod_op     int NOT NULL ,
    cod_com int NOT NULL ,
    costo     int ,
    data_inizio date ,
    data_fine     date ,
    tipo     char(6) NOT NULL ,
    PRIMARY KEY (cod_op,cod_com)
) ;

CREATE TABLE committenti (
    cod_com int             NOT NULL ,
    nome         char(30)     NOT NULL,
    indirizzo char(60) NOT NULL,
    CF             char(16)     unique,
    P_IVA     char(11)     unique,
    tipo         char(8)     NOT NULL ,
    PRIMARY KEY (cod_com)
);


CREATE TABLE appalto (
    cod_op         int     not null references Opere,
    cod_com     int     not null references Opere,
    scadenza     date     not null,
    importo        int     not null,   
    PRIMARY KEY (cod_op,cod_com)
);


But when I try to insert it:
ERROR:  number of referencing and referenced columns for foreign key 
disagree

another table connected at OPERE give instead another error:

CREATE TABLE direzione (
    CF                 char(16)     not null references Salariati(CF),
    cod_op         int             not null references Opere (cod_op),
    cod_com     int              not null references Opere (cod_com),
--    opere_pkey    int    references Opere,
--    PRIMARY KEY (opere_pkey)
    PRIMARY KEY (CF,cod_op,cod_com)
) ;
ERROR:  there is no unique constraint matching given keys for referenced 
table "opere"

If I try to use the index "opere_pkey" (automatic created)
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"opere_pkey" for table "opere"

Why MySQL let me do these and PostgreSQL no?
There's another way?