multiple PK with a non UNIQUE field

Поиск
Список
Период
Сортировка
От davide
Тема multiple PK with a non UNIQUE field
Дата
Msg-id 42BEC2AA.9030404@email.it
обсуждение исходный текст
Ответы Re: multiple PK with a non UNIQUE field
Re: multiple PK with a non UNIQUE field
Список pgsql-sql
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?




В списке pgsql-sql по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: using 'zoneinfo' to manipulate dates ...
Следующее
От: Mathieu Arnold
Дата:
Сообщение: Re: multiple PK with a non UNIQUE field