Re: Delete / F/K error

--- Michael Fuhr <mike(at)fuhr(dot)org> wrote:

> On Fri, Dec 30, 2005 at 12:09:12PM -0800, CSN wrote:
> > ERROR: insert or update on table "types" violates
> > foreign key constraint "$1"
> > DETAIL: Key (page_template_id)=(8) is not present
> in
> > table "templates".
> > CONTEXT: SQL statement "UPDATE ONLY
> "public"."types"
> > SET "item_template_id" = NULL WHERE
> "item_template_id"
> > = $1"
> > SQL statement "DELETE FROM ONLY
> "public"."templates"
> > WHERE "site_id" = $1"
> >
> > In statement:
> > DELETE FROM "sites" WHERE "id"='1'
>
> What are the table definitions for sites, templates,
> and types?
> I'd guess you have some ON DELETE CASCADE and ON
> DELETE SET NULL
> foreign key constraints in templates and types.
> Think through what
> happens when those constraints are triggered by the
> delete on sites;
> somehow you're ending up with a foreign key that
> violates its
> constraint so the delete fails.
>
> What version of PostgreSQL is this?
>
> --
> Michael Fuhr
>

Here's the DDL for types:

CREATE TABLE types (
id integer DEFAULT nextval('"types_id_seq"'::text)
NOT NULL,
name character varying(255) NOT NULL,
item_count integer DEFAULT 0 NOT NULL,
page_template_id integer,
type_template_id integer,
item_template_id integer,
content_template_id integer,
items_template_id integer,
site_id integer NOT NULL
);

ALTER TABLE ONLY types
ADD CONSTRAINT "$1" FOREIGN KEY (page_template_id)
REFERENCES templates(id) ON UPDATE CASCADE ON DELETE
SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT "$2" FOREIGN KEY (type_template_id)
REFERENCES templates(id) ON UPDATE CASCADE ON DELETE
SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT "$3" FOREIGN KEY (item_template_id)
REFERENCES templates(id) ON UPDATE CASCADE ON DELETE
SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT "$4" FOREIGN KEY
(content_template_id) REFERENCES templates(id) ON
UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT "$5" FOREIGN KEY
(items_template_id) REFERENCES templates(id) ON UPDATE
CASCADE ON DELETE SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT fk_types_sites FOREIGN KEY
(site_id) REFERENCES sites(id) ON UPDATE CASCADE ON
DELETE CASCADE;

And templates:

CREATE TABLE templates (
id integer DEFAULT
nextval('"templates_id_seq"'::text) NOT NULL,
name character varying(255) NOT NULL,
type_id integer,
site_id integer
);

ALTER TABLE ONLY templates
ADD CONSTRAINT fk_templates_sites FOREIGN KEY
(site_id) REFERENCES sites(id) ON UPDATE CASCADE ON
DELETE CASCADE;

Hmm, looks like I forgot a templates_types f/k.

Sites doesn't have any f/k's or constraints.

I'm using version 8.0.2.

It turns out there were no corresponding records in
table 'types'. Furthermore, 'delete from templates
where site_id=1;' resulted in this error:
ERROR: insert or update on table "types" violates
foreign key constraint "$1"
DETAIL: Key (page_template_id)=(8) is not present
in table "templates".
CONTEXT: SQL statement "UPDATE ONLY
"public"."types" SET "item_template_id" = NULL WHERE
"item_template_id" = $1"

But I was able to individually delete each template
record, then do 'delete from sites where id=1' with no
resulting errors. I'm still confused what the problem
was.

thanks
csn

__________________________________
Yahoo! for Good - Make a difference this year.
http://brand.yahoo.com/cybergivingweek2005/