Hello Johannes, Since you are using PostgreSQL, you should set all of your constraints to be deferrable. That way the constraint violation checks are only done when the transaction is committed (which is generally when you care about them being correct). This can avoid future constraint issues.
mrg On Wed, Dec 18, 2013 at 5:36 AM, jotpe <[email protected]> wrote: > My fault, again... > > There was a relationship in the ObjEntity "roleGroup" missing. By adding > it, now cayenne deletes first all dependent records sets in > joinrolegrouprole, then all dependent record sets in joinuserrolegroup and > finally the roleGroup itself. > > Fine. > > I thought the two corresponding DbEntity relationships are enough for > detecting, what has to be deleted. > > Regards Johannes > > > 2013/12/18 jotpe <[email protected]> > > > 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 > > > > > > >
