Cascades Failing

From: Jake Stride <nsuk(at)users(dot)sourceforge(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Cascades Failing
Date: 2005-08-16 08:33:05
Message-ID: 4301A4C1.8050508@users.sourceforge.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

I seem to be having some problems with cascading updates, I seem to
remember that this worked in the database in 7.4 but seems to fail in 8,
can anyone give me any pointer please as I seem to be able to find
anything about this online.

I have several tables, but the two I am having issue with are:

\d users
Table "public.users"
Column | Type | Modifiers
------------------+-------------------+-----------
username | character varying | not null
password | character(32) | not null
lastcompanylogin | bigint |
Indexes:
"users_pkey" PRIMARY KEY, btree (username)
Foreign-key constraints:
"$1" FOREIGN KEY (lastcompanylogin) REFERENCES company(id) ON UPDATE
CASCADE ON DELETE CASCADE

\d company
Table "public.company"
Column | Type |
Modifiers
-----------------+-----------------------------+---------------------------------------------------------
id | bigint | not null default
nextval('public.company_id_seq'::text)
name | character varying | not null
accountnumber | character varying | not null
creditlimit | integer |
vatnumber | character varying |
companynumber | character varying |
www | character varying |
employees | integer |
companyid | bigint | not null
branchcompanyid | bigint |
owner | character varying | not null
assigned | character varying |
added | timestamp without time zone | not null default now()
updated | timestamp without time zone | not null default now()
alteredby | character varying |
Indexes:
"company_pkey" PRIMARY KEY, btree (accountnumber, companyid)
"company_accountnumber_key" UNIQUE, btree (accountnumber)
"company_id_key" UNIQUE, btree (id)
"company_accountnumber" btree (accountnumber)
"company_alteredby" btree (alteredby)
"company_assigned" btree (assigned)
"company_branchcompanyid" btree (branchcompanyid)
"company_companyid" btree (companyid)
"company_name" btree (name)
"company_owner" btree ("owner")
Check constraints:
"company_accountdetails" CHECK (name::text <> ''::text AND
accountnumber::text <> ''::text)
"company_branchcompanyid" CHECK (id <> branchcompanyid)
Foreign-key constraints:
"$1" FOREIGN KEY (companyid) REFERENCES company(id) ON UPDATE
CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (branchcompanyid) REFERENCES company(id) ON UPDATE
CASCADE ON DELETE CASCADE
"$3" FOREIGN KEY ("owner") REFERENCES users(username) ON UPDATE
CASCADE ON DELETE CASCADE
"$4" FOREIGN KEY (assigned) REFERENCES users(username) ON UPDATE
CASCADE ON DELETE SET NULL
"$5" FOREIGN KEY (alteredby) REFERENCES users(username) ON UPDATE
CASCADE ON DELETE SET NULL

No when I try to do an update I get the following error:

update users set username='new' where username='old';
ERROR: insert or update on table "company" violates foreign key
constraint "$5"
DETAIL: Key (alteredby)=(old) is not present in table "users".
CONTEXT: SQL statement "UPDATE ONLY "public"."company" SET "assigned" =
$1 WHERE "assigned" = $2"

surely this should not fail because of the 'ON UPDATE CASCADE'?

Thanks

Jake

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oluwatope Akinniyi 2005-08-16 08:58:56 Re: ~/pgpass
Previous Message Magnus Hagander 2005-08-16 08:15:00 Re: libpy and ENABLE_THREAD_SAFETY=1

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Piskorski 2005-08-16 11:45:35 Re: Testing of MVCC
Previous Message Qingqing Zhou 2005-08-16 07:46:22 Re: Race conditions, race conditions!