From: davide <site.webmaster@email.it>
To: pgsql-sql@postgresql.org
Subject: multiple PK with a non UNIQUE field
Date: Sun, 26 Jun 2005 16:58:50 +0200
Message-ID: <42BEC2AA.9030404@email.it> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: site.webmaster@email.it
Subject: Re: multiple PK with a non UNIQUE field
In-Reply-To: <42BEC2AA.9030404@email.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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