pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmultiple PK with a non UNIQUE field
4+ messages / 3 participants
[nested] [flat]
* multiple PK with a non UNIQUE field
@ 2005-06-26 14:58 davide <site.webmaster@email.it>
0 siblings, 2 replies; 4+ messages in thread
From: davide @ 2005-06-26 14:58 UTC (permalink / raw)
To: 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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: multiple PK with a non UNIQUE field
@ 2005-06-26 15:14 Mathieu Arnold <mat@mat.cc>
parent: davide <site.webmaster@email.it>
1 sibling, 0 replies; 4+ messages in thread
From: Mathieu Arnold @ 2005-06-26 15:14 UTC (permalink / raw)
To: davide <site.webmaster@email.it>; pgsql-sql
+-le 26/06/2005 16:58 +0200, davide écrivait :
| 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)
| );
You have to :
CREATE TABLE appalto (
cod_op int not null,
cod_com int not null,
scadenza date not null,
importo int not null,
PRIMARY KEY (cod_op,cod_com),
foreign key (cod_op,cod_com) references opere (cod_op,cod_com)
);
--
Mathieu Arnold
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: multiple PK with a non UNIQUE field
@ 2005-06-26 15:19 Jim Buttafuoco <jim@contactbda.com>
parent: davide <site.webmaster@email.it>
1 sibling, 0 replies; 4+ messages in thread
From: Jim Buttafuoco @ 2005-06-26 15:19 UTC (permalink / raw)
To: davide <site.webmaster@email.it>; 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)
);
to
CREATE TABLE appalto (
cod_op int not null,
cod_com int not null,
scadenza date not null,
importo int not null,
PRIMARY KEY (cod_op,cod_com),
FOREIGN KEY (cod_op,cod_com) REFERENCES Opere(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 -------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: multiple PK with a non UNIQUE field
@ 2005-06-26 20:20 davide <site.webmaster@email.it>
0 siblings, 0 replies; 4+ messages in thread
From: davide @ 2005-06-26 20:20 UTC (permalink / raw)
To: 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)
> );
>
> to
> CREATE TABLE appalto (
> cod_op int not null,
> cod_com int not null,
> scadenza date not null,
> importo int not null,
> PRIMARY KEY (cod_op,cod_com),
> FOREIGN KEY (cod_op,cod_com) REFERENCES Opere(cod_op,cod_com)
> );
>
In this way it works, thanks.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-26 20:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-26 14:58 multiple PK with a non UNIQUE field davide <site.webmaster@email.it>
2005-06-26 15:14 ` Mathieu Arnold <mat@mat.cc>
2005-06-26 15:19 ` Jim Buttafuoco <jim@contactbda.com>
2005-06-26 20:20 Re: multiple PK with a non UNIQUE field davide <site.webmaster@email.it>
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