pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: can't delete record
5+ messages / 3 participants
[nested] [flat]
* Re: can't delete record
@ 2001-03-06 20:28 Michael Fork <mfork@toledolink.com>
parent: Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Fork @ 2001-03-06 20:28 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-interfaces
I believe your foreign key is stopping the delete:
SurID character varying(50) NOT NULL .... ON DELETE RESTRICT,
^^^^^^^^^^^^^^^^^^
Make sure all tuples that SurID references are deleted, then try it
again.
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Tue, 6 Mar 2001, Cedar Cox wrote:
>
> testdb=# select * from tblstsc1options where sc1optionid=1;
> sc1optionid | sc1optionname | sc1optionvalue | surid
> -------------+---------------+----------------+-------
> 1 | blah | | 1.2
> (1 row)
>
> testdb=# delete from tblstsc1options where sc1optionid=1;
> DELETE 0
> testdb=#
>
> --
> I must be really, really tired and I'm missing something. Can anyone
> help ;) FYI, I'm on PG 7.0.2.
>
> As far as I can see, I don't have any rules on this table and the trigger
> function returns new, not null. This is the table definition:
>
> CREATE TABLE tblStSC1Options (
> SC1OptionID int4 NOT NULL,
> SC1OptionName character varying(50) NOT NULL CHECK
> (SC1OptionName<>''),
> SC1OptionValue float4 CHECK (SC1OptionValue>0),
> SurID character varying(50) NOT NULL REFERENCES tblStSC1 ON UPDATE
> CASCADE ON DELETE RESTRICT,
> PRIMARY KEY (SC1OptionID)
> );
>
>
> Thanks,
> -Cedar
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* can't delete record
@ 2001-03-06 20:36 Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 1 reply; 5+ messages in thread
From: Cedar Cox @ 2001-03-06 20:36 UTC (permalink / raw)
To: pgsql-interfaces
testdb=# select * from tblstsc1options where sc1optionid=1;
sc1optionid | sc1optionname | sc1optionvalue | surid
-------------+---------------+----------------+-------
1 | blah | | 1.2
(1 row)
testdb=# delete from tblstsc1options where sc1optionid=1;
DELETE 0
testdb=#
--
I must be really, really tired and I'm missing something. Can anyone
help ;) FYI, I'm on PG 7.0.2.
As far as I can see, I don't have any rules on this table and the trigger
function returns new, not null. This is the table definition:
CREATE TABLE tblStSC1Options (
SC1OptionID int4 NOT NULL,
SC1OptionName character varying(50) NOT NULL CHECK
(SC1OptionName<>''),
SC1OptionValue float4 CHECK (SC1OptionValue>0),
SurID character varying(50) NOT NULL REFERENCES tblStSC1 ON UPDATE
CASCADE ON DELETE RESTRICT,
PRIMARY KEY (SC1OptionID)
);
Thanks,
-Cedar
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Re: can't delete record
@ 2001-03-07 09:44 Cedar Cox <cedarc@visionforisrael.com>
parent: Michael Fork <mfork@toledolink.com>
0 siblings, 0 replies; 5+ messages in thread
From: Cedar Cox @ 2001-03-07 09:44 UTC (permalink / raw)
To: Michael Fork <mfork@toledolink.com>; +Cc: pgsql-interfaces
I think actually you are wrong, for two reasons. 1. If I was having a
problem with referential integrity, I would get an error message when I
tried to delete. 2. This is the many side of the relationship. It is
perfectly valid to delete a record on the many side. In fact, if I do
attempt to delete the record on the one side, I get the error message.
Strike 1! :)
On Tue, 6 Mar 2001, Michael Fork wrote:
> I believe your foreign key is stopping the delete:
>
> SurID character varying(50) NOT NULL .... ON DELETE RESTRICT,
> ^^^^^^^^^^^^^^^^^^
> Make sure all tuples that SurID references are deleted, then try it
> again.
>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Toledo Ohio
>
> On Tue, 6 Mar 2001, Cedar Cox wrote:
>
> >
> > testdb=# select * from tblstsc1options where sc1optionid=1;
> > sc1optionid | sc1optionname | sc1optionvalue | surid
> > -------------+---------------+----------------+-------
> > 1 | blah | | 1.2
> > (1 row)
> >
> > testdb=# delete from tblstsc1options where sc1optionid=1;
> > DELETE 0
> > testdb=#
> >
--
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Re: can't delete record
@ 2001-03-07 14:49 lyonj@EM-Systems.com
parent: Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 0 replies; 5+ messages in thread
From: lyonj@EM-Systems.com @ 2001-03-07 14:49 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-interfaces
I might have read that in Bruce's book. Any serious PostgreSQL developer
should read it cover to cover!
I agree, plpgsql should return an error if you try to access NEW when
TG_OP='DELETE'.
Jeffrey
Cedar Cox wrote:
> Bingo! That was it.. Thank you.
>
> BTW, is this behavior documented (no error message)? It should be.
> Even better, make plpgsql a little nicer ;)
>
> -Cedar
>
> On Wed, 7 Mar 2001, Jeffrey Lyon wrote:
>
> > Do you have a trigger applied against this record on DELETE? If so, are
> > you returning NEW or OLD? I think that you need to return NEW on an
> > UPDATE or INSERT and an OLD on DELETE.
> >
> > Jeffrey
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Re: can't delete record
@ 2001-03-07 15:05 Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 1 reply; 5+ messages in thread
From: Cedar Cox @ 2001-03-07 15:05 UTC (permalink / raw)
To: Jeffrey Lyon <lyonj@em-systems.com>; +Cc: pgsql-interfaces
Bingo! That was it.. Thank you.
BTW, is this behavior documented (no error message)? It should be.
Even better, make plpgsql a little nicer ;)
-Cedar
On Wed, 7 Mar 2001, Jeffrey Lyon wrote:
> Do you have a trigger applied against this record on DELETE? If so, are
> you returning NEW or OLD? I think that you need to return NEW on an
> UPDATE or INSERT and an OLD on DELETE.
>
> Jeffrey
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-03-07 15:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-06 20:36 can't delete record Cedar Cox <cedarc@visionforisrael.com>
2001-03-06 20:28 ` Michael Fork <mfork@toledolink.com>
2001-03-07 09:44 ` Cedar Cox <cedarc@visionforisrael.com>
2001-03-07 15:05 Re: Re: can't delete record Cedar Cox <cedarc@visionforisrael.com>
2001-03-07 14:49 ` lyonj@EM-Systems.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