Re: multiple PK with a non UNIQUE field - Mailing list pgsql-sql

if you need a multi column fk don't use the "references" keyword on your create table, instead use the "FOREIGN KEY" 
keyword for the table, see the "create table" help.  

so for example (untested) change 
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));
 

toCREATE TABLE appalto (    cod_op         int     not null,    cod_com     int     not null,    scadenza     date
notnull,    importo        int     not null,       PRIMARY KEY (cod_op,cod_com),    FOREIGN KEY (cod_op,cod_com)
REFERENCESOpere(cod_op,cod_com));
 


---------- Original Message -----------
From: davide <site.webmaster@email.it>
To: pgsql-sql@postgresql.org
Sent: Sun, 26 Jun 2005 16:58:50 +0200
Subject: [SQL] multiple PK with a non UNIQUE field

> 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?
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
------- End of Original Message -------