pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign key problem
11+ messages / 8 participants
[nested] [flat]
* Foreign key problem
@ 2001-06-25 07:34 Andreas Tille <tillea@rki.de>
0 siblings, 2 replies; 11+ messages in thread
From: Andreas Tille @ 2001-06-25 07:34 UTC (permalink / raw)
To: pgsql-sql
Hello,
I tried to track down the database definitions from a more complex
database which I have to convert from MS SQL to PostgreSQL. I have
only one last syntactical error. Here is the striped down code
to the shortest snipped which shows the problem:
CREATE TABLE ResKulturDetail
(
IdLabNr int,
IdIndex smallint
);
CREATE TABLE ResKulturDetailDay
(
IdLabNr int,
IdIndex smallint
);
CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ;
CLUSTER IX_IdLabNr_KulturDetail ON ResKulturDetail ;
ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay
FOREIGN KEY (IdLabNr,IdIndex)
REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;
Here is the psql log, if I try to insert the code above:
reskultur=# CREATE TABLE ResKulturDetail
reskultur-# (
reskultur(# IdLabNr int,
reskultur(# IdIndex smallint
reskultur(# );
CREATE
reskultur=#
reskultur=# CREATE TABLE ResKulturDetailDay
reskultur-# (
reskultur(# IdLabNr int,
reskultur(# IdIndex smallint
reskultur(# );
CREATE
reskultur=#
reskultur=# CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ;
CREATE
reskultur=# CLUSTER IX_IdLabNr_KulturDetail ON ResKulturDetail ;
CLUSTER
reskultur=#
reskultur=# ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay
reskultur-# FOREIGN KEY (IdLabNr,IdIndex)
reskultur-# REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced table "reskulturdetail" not found
Can anybody explain, why the foreign key constraint fails?
Thanks and have a nice weekend
Andreas.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign key problem
@ 2001-06-25 14:06 Christof Glaser <gcg@gl.aser.de>
parent: Andreas Tille <tillea@rki.de>
1 sibling, 0 replies; 11+ messages in thread
From: Christof Glaser @ 2001-06-25 14:06 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; pgsql-sql
On Monday, 25. June 2001 09:34, Andreas Tille wrote:
> Hello,
>
> I tried to track down the database definitions from a more complex
> database which I have to convert from MS SQL to PostgreSQL. I have
> only one last syntactical error. Here is the striped down code
> to the shortest snipped which shows the problem:
>
> CREATE TABLE ResKulturDetail
> (
> IdLabNr int,
> IdIndex smallint
> );
>
> CREATE TABLE ResKulturDetailDay
> (
> IdLabNr int,
> IdIndex smallint
> );
>
> CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ;
> CLUSTER IX_IdLabNr_KulturDetail ON ResKulturDetail ;
>
> ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay
> FOREIGN KEY (IdLabNr,IdIndex)
> REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;
>
> Here is the psql log, if I try to insert the code above:
[snip]
> reskultur=# ALTER TABLE ResKulturDetailDay ADD CONSTRAINT
> FK_ResKulturDetailDay
> reskultur-# FOREIGN KEY (IdLabNr,IdIndex)
> reskultur-# REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;
> NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit
> trigger(s) for FOREIGN KEY check(s)
> ERROR: UNIQUE constraint matching given keys for referenced table
> "reskulturdetail" not found
Just create an additional UNIQUE index for the referenced fields:
CREATE UNIQUE INDEX need_a_better_name
ON ResKulturDetail ( IdLabNr, IdIndex );
> Can anybody explain, why the foreign key constraint fails?
Foreign keys must reference to unique field(s), as the error message
tries to tell you.
> Thanks and have a nice weekend
"I don't like mondays" comes to my mind :-)
That being said, sometimes there seems to be a weird delay in mail
delivery from PG lists. I get quite sometimes answers first while the
question arrives hours later. This is no complaint, I can live with it.
Christof.
--
gl.aser . software engineering . internet service
http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign key problem
@ 2001-06-25 14:07 Itai Zukerman <zukerman@math-hat.com>
parent: Andreas Tille <tillea@rki.de>
1 sibling, 1 reply; 11+ messages in thread
From: Itai Zukerman @ 2001-06-25 14:07 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: pgsql-sql
Je Mon, 25 Jun 2001 09:34:01 +0200 (CEST),
Andreas Tille <tillea@rki.de> scribis:
> CREATE TABLE ResKulturDetail
> (
> IdLabNr int,
> IdIndex smallint
> );
>
> CREATE TABLE ResKulturDetailDay
> (
> IdLabNr int,
> IdIndex smallint
> );
>
> CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ;
>
> ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay
> FOREIGN KEY (IdLabNr,IdIndex)
> REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;
> Can anybody explain, why the foreign key constraint fails?
According to the documentation for CREATE TABLE:
In addition, the referenced columns are supposed to be the columns
of a UNIQUE constraint in the referenced table, however Postgres
does not enforce this.
Well, it looks like PostgreSQL *does* enforce it. Try it with:
CREATE UNIQUE INDEX IX_IdLabNr_KulturDetail
ON ResKulturDetail(IdLabNr, IdIndex) ;
I'm not sure why this restriction is necessary...
--
Itai Zukerman <http://www.math-hat.com/~zukerman/;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign key problem
@ 2001-06-25 16:22 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Itai Zukerman <zukerman@math-hat.com>
0 siblings, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2001-06-25 16:22 UTC (permalink / raw)
To: Itai Zukerman <zukerman@math-hat.com>; +Cc: Andreas Tille <tillea@rki.de>; pgsql-sql
On 25 Jun 2001, Itai Zukerman wrote:
> According to the documentation for CREATE TABLE:
>
> In addition, the referenced columns are supposed to be the columns
> of a UNIQUE constraint in the referenced table, however Postgres
> does not enforce this.
>
> Well, it looks like PostgreSQL *does* enforce it. Try it with:
Hmm, that paragraph should have been ripped out at the same time I added
the check. Will see if I need to patch the docs (or if it was already
done by someone).
> CREATE UNIQUE INDEX IX_IdLabNr_KulturDetail
> ON ResKulturDetail(IdLabNr, IdIndex) ;
>
> I'm not sure why this restriction is necessary...
Mostly because the semantics of the constraint as defined by the spec
don't make sense in certain cases against non-unique pk table rows. For
example, a delete cascade would delete the *first* time a matched row was
deleted even if there were other rows that could be matched except in
MATCH PARTIAL (which we don't support). Once we support MATCH PARTIAL,
we might make the case to allow MATCH PARTIAL references to non-unique
columns as an extension, but we'd need to think through the other effects
of that.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: foreign key problem
@ 2002-10-07 00:11 Josh Berkus <josh@agliodbs.com>
parent: Laurette Cisneros <laurette@nextbus.com>
2 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2002-10-07 00:11 UTC (permalink / raw)
To: Laurette Cisneros <laurette@nextbus.com>; pgsql-sql
Laurette,
> How is this possible? I've tried to reproduce this, but haven't been able
> to yet. This has happened to use several times.
Question 1: Can you experimentally create a record in table1, a matching
record in table 2, and then delete the record in table1? It's possible that
your "ON DELETE CASCADE" trigger got wiped out somehow.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: foreign key problem
@ 2002-10-07 00:11 Josh Berkus <josh@agliodbs.com>
parent: Laurette Cisneros <laurette@nextbus.com>
2 siblings, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2002-10-07 00:11 UTC (permalink / raw)
To: Laurette Cisneros <laurette@nextbus.com>; pgsql-sql
Laurette,
> How is this possible? I've tried to reproduce this, but haven't been able
> to yet. This has happened to use several times.
Oh, forgot question 2:
SELECT version()?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* foreign key problem
@ 2002-10-08 00:03 Laurette Cisneros <laurette@nextbus.com>
0 siblings, 3 replies; 11+ messages in thread
From: Laurette Cisneros @ 2002-10-08 00:03 UTC (permalink / raw)
To: pgsql-sql
I have two tables:
create table table1
( vers integer,
table1_id text NOT NULL,
desc text,
PRIMARY KEY (rev, table1)id)
);
create table table2
( vers integer,
othertble_id text NOT NULL,
table1_id text,
FOREIGN KEY (rev, othertable_id) REFERENCES othertable
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (rev, table1_id) REFERENCES table1
ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (rev, othertable_id, table1_id)
);
As you can see, table2 has a foreign key reference to table1 and also
includes on delete cascade (and on update cascade).
There has been a lot of activity in this database on these tables in
particular in the last several days and somehow we've ended up with rows in
table2 that have table1_ids that do not exist in table1.
How is this possible? I've tried to reproduce this, but haven't been able
to yet. This has happened to use several times.
Thanks for any help,
--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
------------------------------
It's 10 o'clock...
Do you know where your bus is?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: foreign key problem
@ 2002-10-08 00:21 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Laurette Cisneros <laurette@nextbus.com>
2 siblings, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2002-10-08 00:21 UTC (permalink / raw)
To: Laurette Cisneros <laurette@nextbus.com>; +Cc: pgsql-sql
On Mon, 7 Oct 2002, Laurette Cisneros wrote:
>
> I have two tables:
>
> create table table1
> ( vers integer,
> table1_id text NOT NULL,
> desc text,
> PRIMARY KEY (rev, table1)id)
> );
>
> create table table2
> ( vers integer,
> othertble_id text NOT NULL,
> table1_id text,
> FOREIGN KEY (rev, othertable_id) REFERENCES othertable
> ON UPDATE CASCADE ON DELETE CASCADE,
> FOREIGN KEY (rev, table1_id) REFERENCES table1
> ON UPDATE CASCADE ON DELETE CASCADE,
> PRIMARY KEY (rev, othertable_id, table1_id)
> );
>
> As you can see, table2 has a foreign key reference to table1 and also
> includes on delete cascade (and on update cascade).
>
> There has been a lot of activity in this database on these tables in
> particular in the last several days and somehow we've ended up with rows in
> table2 that have table1_ids that do not exist in table1.
>
> How is this possible? I've tried to reproduce this, but haven't been able
> to yet. This has happened to use several times.
The only thing apart from bugs I can think of would be triggers or rules
that forced the implicit deletes to have another behavior. It'd be
helpful if you can get a representative sequence that reproduces it.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: foreign key problem
@ 2002-10-09 23:16 Laurette Cisneros <laurette@nextbus.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 11+ messages in thread
From: Laurette Cisneros @ 2002-10-09 23:16 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
7.2.3.
It's a mystery as to how this happened but there is a program we use to
copy rows from one version to another. For some reason the person who
wrote it disabled triggers before copying rows to a new version then
re-enabled the triggers. If someone made changes while the triggers were
off then this could happen. At least that's my story and I'm sticking to
it.
So, the program has been fixed and we hope this plugs the hole that that
rabbit went down.
Thanks,
L.
On Sun, 6 Oct 2002, Josh Berkus wrote:
> Laurette,
>
> > How is this possible? I've tried to reproduce this, but haven't been able
> > to yet. This has happened to use several times.
>
> Oh, forgot question 2:
> SELECT version()?
>
>
--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
------------------------------
It's 10 o'clock...
Do you know where your bus is?
^ permalink raw reply [nested|flat] 11+ messages in thread
* foreign key problem
@ 2007-09-24 12:34 ivan marchesini <marchesini@unipg.it>
0 siblings, 1 reply; 11+ messages in thread
From: ivan marchesini @ 2007-09-24 12:34 UTC (permalink / raw)
To: pgsql-sql
Dear users,
I'm trying to create constraints into a db they give me from access...
I'm using pgadmin to create a new foreign key for the relationship
between two table..
I have a table called "generalita" wich contains a column called
"cod_carg":
___________________
select cod_carg from generalita group by cod_carg;
cod_carg
----------
2
0
1
(4 righe)
___________________
I have another table, called "diz_carg" that contain, into a column
called "cod_carg" (that is a primary key), the domain values for the
"cod_carg" column of "generalita":
__________________
select cod_carg from diz_carg group by cod_carg;
cod_carg
----------
1
0
2
(3 righe)
__________________
so when I try to create the foreign key I obtain:
ALTER TABLE generalita ADD CONSTRAINT fkey_diz_cod_carg FOREIGN KEY
(cod_carg) REFERENCES diz_carg (cod_carg) ON UPDATE NO ACTION ON DELETE
NO ACTION;
____________________________
ERROR: insert or update on table "generalita" violates foreign key
constraint "fkey_diz_cod_carg"
DETAIL: Key (cod_carg)=( ) is not present in table "diz_carg".
____________________________
can I allow null values for "generalita.cod_carg" although it is a
foreign key for "diz_carg.cod_carg"?
I apologize if it is a stupid question.... :-(
many thanks to all
--
Ti prego di cercare di non inviarmi files .doc, .xls, .ppt, .dwg.
Preferisco formati liberi.
Please try to avoid to send me .doc, .xls, .ppt, .dwg files.
I prefer free formats.
http://it.wikipedia.org/wiki/Formato_aperto
http://en.wikipedia.org/wiki/Open_format
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a
06125
Perugia (Italy)
e-mail: marchesini@unipg.it
ivan.marchesini@gmail.com
tel: +39(0)755853760
fax (university): +39(0)755853756
fax (home): +39(0)5782830887
jabber: geoivan73@jabber.org
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: foreign key problem
@ 2007-09-26 13:25 Ashish Karalkar <ashish.karalkar@info-spectrum.com>
parent: ivan marchesini <marchesini@unipg.it>
0 siblings, 0 replies; 11+ messages in thread
From: Ashish Karalkar @ 2007-09-26 13:25 UTC (permalink / raw)
To: marchesini@unipg.it; pgsql-sql
----- Original Message -----
From: "ivan marchesini" <marchesini@unipg.it>
To: <pgsql-sql@postgresql.org>
Sent: Monday, September 24, 2007 6:04 PM
Subject: [SQL] foreign key problem
> Dear users,
> I'm trying to create constraints into a db they give me from access...
> I'm using pgadmin to create a new foreign key for the relationship
> between two table..
>
> I have a table called "generalita" wich contains a column called
> "cod_carg":
> ___________________
> select cod_carg from generalita group by cod_carg;
> cod_carg
> ----------
>
> 2
> 0
> 1
> (4 righe)
> ___________________
>
>
> I have another table, called "diz_carg" that contain, into a column
> called "cod_carg" (that is a primary key), the domain values for the
> "cod_carg" column of "generalita":
> __________________
> select cod_carg from diz_carg group by cod_carg;
> cod_carg
> ----------
> 1
> 0
> 2
> (3 righe)
> __________________
>
>
>
> so when I try to create the foreign key I obtain:
> ALTER TABLE generalita ADD CONSTRAINT fkey_diz_cod_carg FOREIGN KEY
> (cod_carg) REFERENCES diz_carg (cod_carg) ON UPDATE NO ACTION ON DELETE
> NO ACTION;
>
> ____________________________
> ERROR: insert or update on table "generalita" violates foreign key
> constraint "fkey_diz_cod_carg"
> DETAIL: Key (cod_carg)=( ) is not present in table "diz_carg".
> ____________________________
>
>
> can I allow null values for "generalita.cod_carg" although it is a
> foreign key for "diz_carg.cod_carg"?
>
> I apologize if it is a stupid question.... :-(
>
> many thanks to all
>
>
Yes you can , check that column cod_carg of table generalita is not set to
NOT NULL.
With regards Ashish
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-09-26 13:25 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-25 07:34 Foreign key problem Andreas Tille <tillea@rki.de>
2001-06-25 14:06 ` Christof Glaser <gcg@gl.aser.de>
2001-06-25 14:07 ` Itai Zukerman <zukerman@math-hat.com>
2001-06-25 16:22 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-08 00:03 foreign key problem Laurette Cisneros <laurette@nextbus.com>
2002-10-07 00:11 ` Re: foreign key problem Josh Berkus <josh@agliodbs.com>
2002-10-07 00:11 ` Re: foreign key problem Josh Berkus <josh@agliodbs.com>
2002-10-09 23:16 ` Re: foreign key problem Laurette Cisneros <laurette@nextbus.com>
2002-10-08 00:21 ` Re: foreign key problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
2007-09-24 12:34 foreign key problem ivan marchesini <marchesini@unipg.it>
2007-09-26 13:25 ` Re: foreign key problem Ashish Karalkar <ashish.karalkar@info-spectrum.com>
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