Cascades Failing
От | Jake Stride |
---|---|
Тема | Cascades Failing |
Дата | |
Msg-id | 4301A4C1.8050508@users.sourceforge.net обсуждение исходный текст |
Ответы |
Re: Cascades Failing
(Stephan Szabo <sszabo@megazone.bigpanda.com>)
Re: Cascades Failing in 8.0.x (Richard Huxton <dev@archonet.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: