From: | CSN <cool_screen_name90001(at)yahoo(dot)com> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Delete / F/K error |
Date: | 2005-12-30 22:38:48 |
Message-ID: | 20051230223848.22468.qmail@web52904.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
--- 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/
From | Date | Subject | |
---|---|---|---|
Next Message | Harry Jackson | 2005-12-30 22:39:30 | Re: Forum Software |
Previous Message | Tom Lane | 2005-12-30 22:30:19 | Re: unique constraint with a null column? |