Thread: can't delete record

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
REFERENCEStblStSC1 ON UPDATE
 
CASCADE ON DELETE RESTRICT,       PRIMARY KEY (SC1OptionID)
);


Thanks,
-Cedar

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
> 
> 

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=# 
> > 
--

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

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