Hello list,
if have a postgres db 9.1 running with cayenne 3.2M1.
My datamodel looks like this.
#1 users can have multiple role groups (and reserve)
#2 role groups can have multiple roles (and reserve)
For #1 there is a join table and for #2 there is another, too.
The table roleGroups contains TWO foreign key constraints:
Table "public.rolegroup"
Column | Type | Modifiers
-------------+--------------------------+-----------
description | character varying(100) |
id | integer | not null
lastchanged | timestamp with time zone |
name | character varying(100) |
Indexes:
"rolegroup_pkey1" PRIMARY KEY, btree (id)
Referenced by:
TABLE "joinrolegrouprole" CONSTRAINT
"joinrolegrouprole_rolegroupid_fkey" FOREIGN KEY (rolegroupid) REFERENCES
rolegroup(id)
TABLE "joinuserrolegroup" CONSTRAINT
"joinuserrolegroup_rolegroupid_fkey" FOREIGN KEY (rolegroupid) REFERENCES
rolegroup(id)
When deleting a roleGroup with existing foreign keys, these refereces
should be nullified, by deleting the right recordsets from the join tables.
What happens actually in my point of view:
Cayenne knows my roleGroup id is 200!
First he retrieves the role ids
Information: --- transaction started.
Information: SELECT DISTINCT "t0"."description", "t0"."lastchanged",
"t0"."name", "t0"."id" FROM "public"."role" "t0" JOIN
"public"."joinrolegrouprole" "t1" ON ("t0"."id" = "t1"."roleid") WHERE
"t1"."rolegroupid" = ? [bind: 1->rolegroupid:200]
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logSelectCount
Information: === returned 2 rows. - took 15 ms.
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logCommitTransaction
Information: +++ transaction committed.
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logBeginTransaction
Information: --- transaction started.
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQuery
Information: SELECT "id" AS pk0 FROM "public"."joinrolegrouprole" WHERE
"rolegroupid" = ? AND "roleid" = ? [bind: 1:200, 2:201]
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logSelectCount
Information: === returned 1 row. - took 16 ms.
Now he knows which record sets should be deleted from joinrolegrouprole
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQuery
Information: SELECT "id" AS pk0 FROM "public"."joinrolegrouprole" WHERE
"rolegroupid" = ? AND "roleid" = ? [bind: 1:200, 2:200]
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logSelectCount
Information: === returned 1 row. - took 0 ms.
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQuery
Information: UPDATE "public"."role" SET "lastchanged" = ? WHERE "id" = ?
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQueryParameters
Information: [batch bind: 1->lastchanged:'2013-12-18 10:28:31.531',
2->id:200]
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQueryParameters
Information: [batch bind: 1->lastchanged:'2013-12-18 10:28:31.531',
2->id:201]
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logUpdateCount
Information: === updated 2 rows.
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQuery
Information: DELETE FROM "public"."joinrolegrouprole" WHERE "id" = ?
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQueryParameters
Information: [batch bind: 1->id:200]
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQueryParameters
Information: [batch bind: 1->id:201]
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logUpdateCount
Information: === updated 2 rows.
BUT NOW cayenne wants to delete the rolegroup directly. No record set of
the other join table (joinuserrolegroup) has been deleted yet. This will
cause a foreign key violation.
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQuery
Information: DELETE FROM "public"."rolegroup" WHERE "id" = ?
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQueryParameters
Information: [batch bind: 1->id:200]
Dez 18, 2013 10:28:31 AM org.apache.cayenne.log.CommonsJdbcEventLogger
logQueryError
Information: *** error.
org.postgresql.util.PSQLException: FEHLER: Aktualisieren oder Löschen in
Tabelle »rolegroup« verletzt Fremdschlüssel-Constraint
»joinuserrolegroup_rolegroupid_fkey« von Tabelle »joinuserrolegroup«
Detail: Auf Schlüssel (id)=(200) wird noch aus Tabelle
»joinuserrolegroup« verwiesen.
Cayenne has to know, there is one join table left. Why doesn't cayenne pay
attention to this?
I can avoid this by creating the database scheme with the modeler without
foreign key support, but it is the right solution?
Regards Johannes
