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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
