Search Postgresql Archives

Cascades Failing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux