I found the origin of my problem very easily. The fact is that I didn't know
that one could see the tables' relationships within Navicat. When I checked my
faculty table it turned out that there were 5 relationships between the two
fields. The problem was, obviously, that adding a new foreign key clause
doesn't delete the previous one. I didn't know that. Now I can delete the
parent record.
----- Original Message -----
From: Félix Sánchez Rodríguez
To: pgsql-admin@postgresql.org
Sent: Sunday, April 26, 2009 4:47 PM
Subject: Re: [ADMIN] Deleting "parent" record
The parent table is called "login" and the child one is "faculty".
The error says:
ERROR: null value in column "idlogin" violates not null-constraint
CONTEXT: SQL statement "UPDATE ONLY "public"."faculty" SET "idlogin"=NULL
WHERE "idlogin" = "$1"
To be honest, I created a new test DB and added a parent and a child table
with ON DELETE CASCADE and when I deleted the parent record it was all OK. I'm
wondering if the problem could be that the "login" and "faculty" tables were
not initially created with ON DELETE CASCADE on their relationship, but
instead, I modified it later using a query. Any case, here I post both tables'
structure.
Create table "login"
(
"idlogin" Serial NOT NULL,
"user" Varchar(15) NOT NULL UNIQUE,
"passqword" Varchar(15) NOT NULL,
primary key ("idlogin")
) Without Oids;
Create table "faculty"
(
"idfaculty" Serial NOT NULL,
"idlogin" Integer NOT NULL,
"name" Varchar(30) NOT NULL UNIQUE,
"vicename" Varchar(25) NOT NULL,
"lastname" Varchar(35) NOT NULL,
primary key ("idfacultad")
) Without Oids;
----- Original Message -----
From: Ries van Twisk
To: Félix Sánchez Rodríguez
Cc: pgsql-admin@postgresql.org
Sent: Friday, April 24, 2009 11:12 PM
Subject: Re: [ADMIN] Deleting "parent" record
On Apr 24, 2009, at 10:00 AM, Félix Sánchez Rodríguez wrote:
Hi:
I can't delete a "parent" record from a table. The relation has "ON
UPDATE CASCADE ON DELETE CASCADE". As far as know, that causes that when you
delete a record from the parent table, the related one from the child table
will be deleted as well. However, I had to first delete the child record and
then delete the parent one. Is this normal on using PostgreSQL?? I first tried
the removal by using PHP. When it failed, I thought it was a problem of my PHP
code and I checked it manually from Navicat, it failed too.
You need to tell is the exact reason why it failed, and possible post your
table structure here without that we cannot do much.
Ries
