John Kaufmann wrote:
In a message dated 2009.04.09 02:06 -0500, Ocke Janssen wrote:
In Base's Relationship pane... how is the cascade direction determined?
...
a typical concern is that if an Employee is deleted, the linked
EmployeePhoto record is deleted - but not the other way around: One
might need to delete or change an EmployeePhoto without deleting or
changing the Employee. So there is a clear unidirectional cascade.
How does Base determine that direction?
That depends on the database you use. As Base itself doesn't care, the
database has the responsibility to do the right.
But that's my question: Base does not seem to care about direction - but
that is the front end to the database. So how does the back end know
what we want?
... But in the relation ships design you can select what should happen on
each side differently. So in your example just define that when an
employee is deleted than his photo should be deleted as well and when
only his photo is deleted the employee stays untouched.
First - I always get in trouble when I jump in after one of the core
developers already commented ...but..What the heck?
But, again, can Base offer any help in defining that relationship?
Ok - if you have the relationship defined already then you can double
click on the line that represents this relation, this opens the Relation
Dialog.
At the top of the dialog are two drop down lists under "Tables Involved"
The table in the left drop down is the "referencing table" and the table
in the right drop down is the "referenced table"
So in your case the dialog should list the "EmployeePhoto" table on the
left and the "Employee" table will be on the right.
Put another way, you are creating a Foreign Key constraint on the
"EmployeePhoto" table that refers to the "Employee" table.
Update and Delete settings are always applied per the SQL standard. In
my words - not the standards - Cascade and Delete settings come into
play ONLY when the record being "referred to" is updated or deleted.
So in this case you are telling the database engine what to do if the
record in the Employee table is updated (and specifically only the field
referred to) or deleted.
Updating or Deleting the record in the "referencing table", the
EmployeePhoto table, has no effect on the "referenced table", the
Employee table whatsoever.
Now then however, you toss in another wrinkle here..when you mention a
1..1 relationship between Employee and EmployeePhoto.
Truth is you can't create a 1..1 relationship with Base.
What you can create is a 1..(0 or 1) relationship and that is fairly easy.
Let's say I have an Employee table with a field EmployeeID (integer,
auto_generated) as the Primary Key.
I create a EmployeePhoto table with EmployeePhotoID (integer,
auto_generated) as the Primary Key and a EmployeeID (integer) for the
Foreign Key field.
Now if I create a relation in the relationship window notice that the
line connecting the two tables has a 1 next to Employee and a n next to
EmployeePhoto.
However if I instead create my EmployeePhoto table as
EmployeeID (integer) Primary Key [not this is not auto_generated]
Photo (image)
Now when I use the relationship window to create that relation there is
a 1 next to Employee and a 1 next to EmployeePhoto.
The EmployeePhoto.EmployeeID field is both the Primary Key and a Foreign
Key, creating the 1..(0 or 1) relation.
[Note - this is not the only way to achieve this, but I think it is the
cleanest]
The update and delete settings on this relation function just the same
as they did with the 1..n relations, with a small change in that you are
limited as to what you can do when the referenced record is deleted,
pretty much you must delete this record.
Anyway - I hope that helps...not sure I actually explained it all that
well, so ask further is you like.
Best regards,
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
