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