Comments (7)
Can you give an example? I don't understand how something can have a foreign key constraint but be non-dependent.
Does your solution imply that under certain conditions the delete will not cascade properly and one will have to issue multiple deletes starting from different tables?
from datajoint-matlab.
Here is an example with three tables x.A
, x.B
, and x.C
.
%{
x.A (manual) # test dummy
a : int # A's id
%}
%{
x.B (manual) # test dummy
b : int # B's id
----
-> x.A
%}
%{
x.C (manual) # test dummy
-> x.B
%}
Currently, del(x.A & 'a=1')
will first recurse down the hierarchy to delete x.C & (x.A & 'a=1')
and x.B & (x.A & 'a=1')
. Since x.A and x.C do not share any fields in common, deleting x.C & (x.A & 'a=1')
will delete everything in x.C
.
The problem arises because x.B
makes a non-primary reference to x.A
. New users rarely use non-primary references, so the problem hasn't come up despite being there all along.
Proposed fix
I will fix this by making the delete explicitly refuse to cascade across non-primary references. I just need to come up with a very clear message, something like "Table x.B has dependent tuples. Please delete them first before deleting from x.A".
An alternative solution is to rely on MySQLs cascading delete functionality. Unfortunately, it does not seem to provide a way to list the deleted contents before committing. It also has problems resolving multiple dependence paths. So for now we are forced to use our own cascading mechanisms.
from datajoint-matlab.
Can we not use the correct restriction for cascading in such a case? Wouldn't x.C & (x.B & (x.A & 'a=1'))
work? In other words, every time we encounter a non-PK reference, we use that table for any restrictions down the hierarchy. Potential caveat: restrictions can become quite complex if the hierarchy is deep. However, the complexity should depend on the number of non-PK references, which are usually small. Am I missing something?
from datajoint-matlab.
Yes, that solution would work correctly but in many cases could be prohibitively slow. We do provide non-cascading delete dj.Relvar/delQuick, which could be used in cases where the overhead associated with cascading deletes is excessive.
So we have three alternatives:
- A solution that relies on MySQL's cascade functionality. Fast and correct but has issues. Requires upgrading existing schema to use ON DELETE CASCADE.
- Refuse to cascade across non-PK references. Prompts the user to explicitly delete from the dependent table.
- Cascade across non-PK reference by adjusting the restriction. Correct but potentially slow.
Does option 3 make the most sense?
from datajoint-matlab.
Yes I think so. We can issue a warning that this may be slow but I would argue it's the logical solution since it doesn't remove any functionality.
Also, think about what you would do if DJ told you it can't cascade the delete. You'd manually issue the exact query option 3 would do, in which case there would be no speed benefit for option 2 anyway.
I'm pretty sure option 1 isn't going to work in pretty much any of our schemas.
from datajoint-matlab.
okay, implementing solution 3.
from datajoint-matlab.
Fixed.
from datajoint-matlab.
Related Issues (20)
- Markdown not properly rendered in FEX Toolbox description
- fetchn(tableA * tableB, 'field') does not work in 3.4.2
- del deletes some incorrect entries from downstream tables with renamed foreign keys HOT 1
- table classes on path was not recognized when deleting or populating
- error with fetchn DataJoint version 3.4.2 HOT 1
- `{}` is not supported in attribute comments HOT 1
- bool and boolean is not supported in 3.4.1 HOT 1
- Parallelize file transfer for external blobs HOT 1
- bug with del() HOT 6
- Occasional fwrite errors using file-based stores
- fetching 'KEY' error HOT 2
- discovering existing tables across python/matlab HOT 5
- Empty matrices are converted to NULL HOT 2
- syncDef missing [nullable] & comment properties HOT 2
- Officially use the `+` operator for unions
- SSL Connection error HOT 2
- Schema name must start with lower case HOT 2
- Allow strings as well as char HOT 2
- MATLAB hangs and ramps up CPU usage if user closes the password Diagloue Box
- Allow pasting when logging into a database
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from datajoint-matlab.