Forum OpenACS Q&A: <unnamed> referential integrity violation

Collapse
1: <unnamed> referential integrity violation
Posted by Glen Stewart on 08/29/00 02:19 PM 
We see this error after trying to delete users groups that we just
created, and which have no members.  How do we delete the groups?


Errors since 29/Aug/2000:08:00:01

[29/Aug/2000:08:06:35]
    Error: Ns_PgExec: result status: 7 message: ERROR:  
referential integrity violation - key in user_groups still referenced
from edu_department_info
    

[29/Aug/2000:08:06:35]
    Error: nsd.tcl: Database operation "dml" failed
    Database operation "dml" failed
        while executing
    "ns_db dml $db "delete from user_groups where group_id =
$group_id""
        (file "/pub/www/acs/www/admin/ug/group-delete-2.tcl" line 152)
        invoked from within
    "source $script"
        (procedure "ns_sourceproc" line 6)
        invoked from within
    "ns_sourceproc cns5120 {}"
    Notice: info table name = edu_department_info, exist = 1
Collapse
2: Response to <unnamed> referential integrity violation (response to 1)
Posted by Roberto Mello on 08/29/00 02:34 PM
There are reeferences to that group it the edu_department_info table that need to be deleted _before_ deleting the group in the user_groups table.

It's hard to foresee all the possibilites of deletes during the porting process. In Oracle this is easier because they have on delete cascade.

In a simple way: Scroll your mouse over the group you're thying to delete and notice (through the URL) what is the group_id number (I'll call it x). Then open a terminal and as your AOLserver user (usually nsadmin) run psql and from there:

delete from edu_department_info where group_id=x;

delete from user_groups where group_id=x;

If there are other referential integrity violations, you'll have to delete those too. Please add this bug to the SDM at openacs.org/sdm.

Collapse
3: Response to <unnamed> referential integrity violation (response to 1)
Posted by Don Baccus on 08/30/00 04:18 AM
<blockquote><i>It's hard to foresee all the possibilites of deletes during the porting process. In Oracle this is easier because they have on delete cascade. </i></blockquote>
Actually, Postgres also has "on delete cascade", aD typically hasn't used it in many cases where it would be appropriate, and their release  of the toolkit versions have been notorious for not deleting all necessary tables in the right order.
<p>
The education module broke portals (I fixed it in OpenACS months ago, aD fixed it for ACS Classic in 3.4), I'm not surprised it has other bugs.
<p>
This should go into the SDM, if someone wants to track it down and fix it, too, that would be wonderful.
Collapse
4: Response to <unnamed> referential integrity violation (response to 1)
Posted by Roberto Mello on 08/30/00 06:24 AM
Thanks for the info Don. I didn't know PG had on delete cascade and that's great. I guess I need to read its docs more 😊 PG surprises me more everyday.