Hi monetdb people,
I'm doing some work again on Djonet, implementing missing features. One of the features is to flush the content of a set tables e.g. remove all rows. The problem is that the order the tables are processed is important in the case of foreign key relations.
They way this is implemented in other database drivers for django is to temporarily disable foreign key relations (mysql) or to TRUNCATE multiple tables in one statement (postgresql). Is there an (undocumented) way of accomplishing something similar? I prefer not to temporarily alter the table.
Thanks,
Hi Gijs
No. Foreign key constraints are there for a good reason and disabling them creates a, albeit temporarily, inconsistent database state. It is the client side, i.e. DBA, that has to decide on this. For this he can take a mserver offline for maintenance (using monetdb) and perform the necessary actions.
There no TRUNCATE multiple tables command.
regards, Martin
On 6/7/13 2:21 PM, Gijs Molenaar wrote:
Hi monetdb people,
I'm doing some work again on Djonet, implementing missing features. One of the features is to flush the content of a set tables e.g. remove all rows. The problem is that the order the tables are processed is important in the case of foreign key relations.
They way this is implemented in other database drivers for django is to temporarily disable foreign key relations (mysql) or to TRUNCATE multiple tables in one statement (postgresql). Is there an (undocumented) way of accomplishing something similar? I prefer not to temporarily alter the table.
Thanks,
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi Martin,
Thanks for your answer. I’ve implemented a topological sort for the foreign key relations. This works, but not for cyclic references. I know one should not create cyclic references, but it can still happen. In this case, there is no way to automatically flush these tables, right?
- Gijs
On 07/06/13 15:39, Martin Kersten wrote:
Hi Gijs
No. Foreign key constraints are there for a good reason and disabling them creates a, albeit temporarily, inconsistent database state. It is the client side, i.e. DBA, that has to decide on this. For this he can take a mserver offline for maintenance (using monetdb) and perform the necessary actions.
There no TRUNCATE multiple tables command.
regards, Martin
On 6/7/13 2:21 PM, Gijs Molenaar wrote:
Hi monetdb people,
I'm doing some work again on Djonet, implementing missing features. One of the features is to flush the content of a set tables e.g. remove all rows. The problem is that the order the tables are processed is important in the case of foreign key relations.
They way this is implemented in other database drivers for django is to temporarily disable foreign key relations (mysql) or to TRUNCATE multiple tables in one statement (postgresql). Is there an (undocumented) way of accomplishing something similar? I prefer not to temporarily alter the table.
Thanks,
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
On 07-06-2013 16:29:35 +0200, Gijs Molenaar wrote:
Hi Martin,
Thanks for your answer. I’ve implemented a topological sort for the foreign key relations. This works, but not for cyclic references. I know one should not create cyclic references, but it can still happen. In this case, there is no way to automatically flush these tables, right?
within a transaction you can insert those statements, at the time you do COMMIT it should all be satisfied, though
On 08/06/13 15:50, Fabian Groffen wrote:
On 07-06-2013 16:29:35 +0200, Gijs Molenaar wrote:
Hi Martin,
Thanks for your answer. I’ve implemented a topological sort for the foreign key relations. This works, but not for cyclic references. I know one should not create cyclic references, but it can still happen. In this case, there is no way to automatically flush these tables, right?
within a transaction you can insert those statements, at the time you do COMMIT it should all be satisfied, though
Hi Fabian,
I'm not sure what you say here. If I try to remove the rows in a transaction I also get a foreign key violation error.
Thanks,
- Gijs
participants (3)
-
Fabian Groffen
-
Gijs Molenaar
-
Martin Kersten