pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReferential Integrity
11+ messages / 10 participants
[nested] [flat]
* Referential Integrity
@ 1998-05-13 20:28 Mike Lemler <coronach@datacruz.com>
0 siblings, 1 reply; 11+ messages in thread
From: coronach@datacruz.com @ 1998-05-13 20:28 UTC (permalink / raw)
To: pgsql-sql
I haven't been able to find documentation on how to implement referential
integrity between tables with postgres via sql. Could someone please post
an example. Thanks.
Michael.
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: Referential Integrity
@ 1998-05-13 21:06 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jackson, DeJuan @ 1998-05-13 21:06 UTC (permalink / raw)
To: coronach@datacruz.com; pgsql-sql
Take a look at:
PGDIST/contrib/spi/refint.c
-DEJ
> -----Original Message-----
> From: coronach@datacruz.com [SMTP:coronach@datacruz.com]
> Sent: Wednesday, May 13, 1998 3:29 PM
> To: pgsql-sql@postgreSQL.org
> Subject: Referential Integrity
>
> I haven't been able to find documentation on how to implement
> referential
> integrity between tables with postgres via sql. Could someone please
> post
> an example. Thanks.
>
> Michael.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Referential Integrity
@ 1998-05-14 10:57 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Mike Lemler <coronach@datacruz.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jose' Soares Da Silva @ 1998-05-14 10:57 UTC (permalink / raw)
To: Mike Lemler <coronach@datacruz.com>; +Cc: pgsql-sql
On Wed, 13 May 1998, Mike Lemler wrote:
> I haven't been able to find documentation on how to implement referential
> integrity between tables with postgres via sql. Could someone please post
> an example. Thanks.
>
> Michael.
>
Table Constraint definition:
[ CONSTRAINT name ]
{ PRIMARY KEY constraint |
UNIQUE constraint |
CHECK constraint }
Column Constraint definition:
[ CONSTRAINT name ]
{ NOT NULL constraint |
PRIMARY KEY constraint |
UNIQUE constraint |
CHECK constraint }
examples:
--defines a CHECK column constraint:
--
CREATE TABLE distributors (
did DECIMAL(3) CHECK (did > 100),
name VARCHAR(40)
);
--defines a CHECK table constraint:
--
CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40)
CONSTRAINT con1 CHECK (did > 100 AND name > '')
);
PS: FOREIGN KEY is not implemented yet you can use TRIGGERS instead
take a look at ...contrib/refint*
Jose'
^ permalink raw reply [nested|flat] 11+ messages in thread
* Referential Integrity
@ 1999-07-08 18:41 Gilson Costa <gpcosta@ruralsp.com.br>
0 siblings, 1 reply; 11+ messages in thread
From: Gilson Costa @ 1999-07-08 18:41 UTC (permalink / raw)
To: pgsql-sql
I am a novice in PostgreSQL.
How can I impose referential integrity to my tables?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Referential Integrity
@ 1999-07-08 20:38 Steven Bradley <sbradley@llnl.gov>
parent: Gilson Costa <gpcosta@ruralsp.com.br>
0 siblings, 0 replies; 11+ messages in thread
From: Steven Bradley @ 1999-07-08 20:38 UTC (permalink / raw)
To: Gilson Costa <gpcosta@ruralsp.com.br>; +Cc: pgsql-sql
PostgreSQL does not implement declarative referential integrity. It lets
you specify foreign key constraints in table definitions, but does nothing
with them. As such, you have to resort to writing triggers to implement the
traditional RI concerns (e.g.: mandatory parent, cascade delete, etc...).
At 03:41 PM 7/8/99 -0300, you wrote:
>I am a novice in PostgreSQL.
>How can I impose referential integrity to my tables?
>
>
>
>
Steven Bradley
Lawrence Livermore National Laboratory
PO Box 808
Livermore, California 94550
(925) 423-2101 sbradley@llnl.gov
^ permalink raw reply [nested|flat] 11+ messages in thread
* referential integrity
@ 1999-08-04 01:47 Ramanika <ramanika@flashmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Ramanika @ 1999-08-04 01:47 UTC (permalink / raw)
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
I did not see this in the documentation anywhere. Does postgresql
support referential integrity? like when creating a table REFERENCES
table(column_id)?
thanks
Ramanika
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] referential integrity
@ 1999-08-04 11:08 Howie <caffeine@toodarkpark.org>
parent: Ramanika <ramanika@flashmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Howie @ 1999-08-04 11:08 UTC (permalink / raw)
To: Ramanika <ramanika@flashmail.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgreSQL.org>; pgsql-sql
On Wed, 4 Aug 1999, Ramanika wrote:
> I did not see this in the documentation anywhere. Does postgresql
> support referential integrity? like when creating a table REFERENCES
> table(column_id)?
not directly, no. at least not yet :) 6.6 is rumoured to have full
support.
for now you can use refint ( $PGSQL_SRC_ROOT/contrib/spi/refint.* ), which
handles referential integrity. check_primary_key() and
check_foreign_key() are the two functions involved. youd also need two
triggers, one per table.
i could've sworn this got added to the FAQ...
---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Referential Integrity
@ 2003-02-10 15:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexander Stanier <alexander.stanier@egsgroup.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2003-02-10 15:09 UTC (permalink / raw)
To: Alexander Stanier <alexander.stanier@egsgroup.com>; +Cc: pgsql-sql
"Alexander Stanier" <alexander.stanier@egsgroup.com> writes:
> [ expected ON DELETE CASCADE doesn't seem to happen in this context: ]
> CREATE FUNCTION test() RETURNS INT4 AS '
> DECLARE
> v_return INTEGER;
> BEGIN
> DELETE FROM alex
> WHERE aid =3D ''1''
> AND bid =3D ''1'';
> INSERT INTO alex (aid,bid,itemdesc)
> VALUES (''1'',''1'',''OneOne'');
> INSERT INTO alexette (aid,bid,vcode)
> VALUES (''1'',''1'',''V'');
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';
The cascaded deletes will be implemented at end of statement --- which
I believe is always taken to be the end of the current interactive
statement, ie, the "SELECT test()" you typed. So they haven't happened
yet at the time the function tries to do the inserts.
This is probably a bug, but IIRC, it's not entirely obvious what to do
instead. I seem to recall some inconclusive discussions in pgsql-hackers
about designing a better rule for when to fire the RI actions. Check
the list archives for details.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Referential Integrity
@ 2003-02-10 15:54 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2003-02-10 15:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alexander Stanier <alexander.stanier@egsgroup.com>; pgsql-sql
On Mon, 10 Feb 2003, Tom Lane wrote:
> "Alexander Stanier" <alexander.stanier@egsgroup.com> writes:
> > [ expected ON DELETE CASCADE doesn't seem to happen in this context: ]
>
> > CREATE FUNCTION test() RETURNS INT4 AS '
> > DECLARE
> > v_return INTEGER;
> > BEGIN
> > DELETE FROM alex
> > WHERE aid =3D ''1''
> > AND bid =3D ''1'';
>
> > INSERT INTO alex (aid,bid,itemdesc)
> > VALUES (''1'',''1'',''OneOne'');
>
> > INSERT INTO alexette (aid,bid,vcode)
> > VALUES (''1'',''1'',''V'');
>
> > RETURN 0;
> > END;
> > ' LANGUAGE 'plpgsql';
>
> The cascaded deletes will be implemented at end of statement --- which
> I believe is always taken to be the end of the current interactive
> statement, ie, the "SELECT test()" you typed. So they haven't happened
> yet at the time the function tries to do the inserts.
>
> This is probably a bug, but IIRC, it's not entirely obvious what to do
> instead. I seem to recall some inconclusive discussions in pgsql-hackers
> about designing a better rule for when to fire the RI actions. Check
> the list archives for details.
Yeah, I don't think we came to a conclusion. Looking at SQL99, the
"correct" behavior seems to be that the actions occur as part of the
actual delete or update action so that effectively at the "same" point in
time the dependant rows are changed, using (I think) 14.14 and 14.20 as a
guide. This is significantly different from what we do AFAICT though. :(
(For example, I'm not sure if 14.14 implies that after triggers from the
statement inside the function to occur as part of that statement rather
than after the function ends)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Referential Integrity
@ 2003-02-10 16:09 Alexander Stanier <alexander.stanier@egsgroup.com>
0 siblings, 0 replies; 11+ messages in thread
From: Alexander Stanier @ 2003-02-10 16:09 UTC (permalink / raw)
To: pgsql-sql; +Cc: sszabo@megazone23.bigpanda.com; janwieck@Yahoo.com
Tom,
Thanks for your reply. I did find some correspondance between Stephan
Szabo mailto:sszabo@megazone23.bigpanda.com and Jan Wieck
mailto:janwieck@Yahoo.com entitled "Subject: Re: Questions on RI spec
(poss. bugs)".
I can see that if the delete statement called a function which deleted
the same record then it might cause problems. It does however seem to me
that if a constraint is not deferred then the statement executor should
see results of that command, otherwise the executor has lost consistency
in the transaction. The docs do say "A constraint that is not deferrable
will be checked immediately after every command." But, I guess it is a
question of, having checked the constraint - when to fire the "on
cascade delete" trigger? I am not sure exactly what SQL99 says, but the
way it works seems wrong to me.
I guess I could rewrite the code to update the relevant records instead
of deleting and re-inserting - but then this becomes more complicated.
If you want to replace a whole lot of related records it is much easier
to do one delete and then reinsert them.
Had a look through the FAQs and TODO, but can't see anything related to
this problem. I'll see what replies I get from this before reporting it
as a bug.
Regards,
Alex.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 10 February 2003 15:10
To: Alexander Stanier
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Referential Integrity
"Alexander Stanier" <alexander.stanier@egsgroup.com> writes:
> [ expected ON DELETE CASCADE doesn't seem to happen in this context: ]
> CREATE FUNCTION test() RETURNS INT4 AS '
> DECLARE
> v_return INTEGER;
> BEGIN
> DELETE FROM alex
> WHERE aid =3D ''1''
> AND bid =3D ''1'';
> INSERT INTO alex (aid,bid,itemdesc)
> VALUES (''1'',''1'',''OneOne'');
> INSERT INTO alexette (aid,bid,vcode)
> VALUES (''1'',''1'',''V'');
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';
The cascaded deletes will be implemented at end of statement --- which
I believe is always taken to be the end of the current interactive
statement, ie, the "SELECT test()" you typed. So they haven't happened
yet at the time the function tries to do the inserts.
This is probably a bug, but IIRC, it's not entirely obvious what to do
instead. I seem to recall some inconclusive discussions in pgsql-hackers
about designing a better rule for when to fire the RI actions. Check
the list archives for details.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Referential Integrity
@ 2003-02-11 13:08 Alexander Stanier <alexander.stanier@egsgroup.com>
0 siblings, 1 reply; 11+ messages in thread
From: Alexander Stanier @ 2003-02-11 13:08 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
This was originally logged under pgsql-sql, as "[SQL] Referential
Integrity", but after some discussion it seems that this is a bug.
I have the following scenario in pg7.3.1.:
CREATE TABLE alex (aid varchar(40) NOT NULL,
bid varchar(100) NOT NULL,
itemdesc varchar(100),
CONSTRAINT alex_pkey PRIMARY KEY (aid, bid));
CREATE TABLE alexette (aid varchar(40) NOT NULL,
bid varchar(100) NOT NULL,
vcode varchar(1),
CONSTRAINT alexette_pkey PRIMARY KEY (aid, bid));
ALTER TABLE alexette
ADD CONSTRAINT alexette_fk_1 FOREIGN KEY (aid,bid)
REFERENCES alex (aid,bid)
ON DELETE CASCADE;
INSERT INTO alex VALUES ('1','1','OneOne');
INSERT INTO alexette VALUES ('1','1','V');
CREATE FUNCTION test() RETURNS INT4 AS '
DECLARE
v_return INTEGER;
BEGIN
DELETE FROM alex
WHERE aid = ''1''
AND bid = ''1'';
INSERT INTO alex (aid,bid,itemdesc)
VALUES (''1'',''1'',''OneOne'');
INSERT INTO alexette (aid,bid,vcode)
VALUES (''1'',''1'',''V'');
RETURN 0;
END;
' LANGUAGE 'plpgsql';
Please could someone tell me why running the function test() causes the
following error:
"Cannot insert a duplicate key into unique index alexette_pkey"
The foreign key with "on delete cascade" should ensure that the delete
statement within this function should delete the records from both alex
and alexette. Indeed a simple "delete from alex" demonstrates that this
does indeed work. However, it appears that within the function this is
not happening or that the insert statements cannot see the full extent
of the changes made by the delete statement. Whichever, surely this is
wrong?
Regards,
Alexander Stanier.
mailto:alex@egsgroup.com
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-02-11 13:08 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-05-13 20:28 Referential Integrity Mike Lemler <coronach@datacruz.com>
1998-05-14 10:57 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-05-13 21:06 RE: Referential Integrity Jackson, DeJuan <djackson@cpsgroup.com>
1999-07-08 18:41 Referential Integrity Gilson Costa <gpcosta@ruralsp.com.br>
1999-07-08 20:38 ` Steven Bradley <sbradley@llnl.gov>
1999-08-04 01:47 referential integrity Ramanika <ramanika@flashmail.com>
1999-08-04 11:08 ` Re: [SQL] referential integrity Howie <caffeine@toodarkpark.org>
2003-02-10 16:09 Re: Referential Integrity Alexander Stanier <alexander.stanier@egsgroup.com>
2003-02-11 13:08 Referential Integrity Alexander Stanier <alexander.stanier@egsgroup.com>
2003-02-10 15:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-10 15:54 ` Stephan Szabo <sszabo@megazone23.bigpanda.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