SQL Anywhere Mobile - referential integrity

Author

referential integrity

matt

2005-10-29, 1:26 pm

Is there a tech doc on how to implement referential integrity on a remote??
Am having major amount of deletes done when RI kicks in and cant find
the source of the problem.
What is the consensus of allowing nulls on fk columns?

ASA 9.02

Thanks

Greg Fenton

2005-10-30, 3:25 am

matt wrote:
> Is there a tech doc on how to implement referential integrity on a remote??

You implement referential integrity the exact same way in a remote as in
a consolidated, with a FOREIGN KEY.

> Am having major amount of deletes done when RI kicks in and cant find
> the source of the problem.

Can you explain what this problem is, and what you mean by "when RI
kicks in"?

> What is the consensus of allowing nulls on fk columns?

Nulls are allowed in FK columns if they are defined that way (i.e. not
declared NOT NULL).

Note that if, during synchronization, a parent record is deleted by the
download stream, then any associated children records will also
automatically be deleted.

In the SQLAnywher 9.x online docs, see:

MobiLink Clients
Dbmlsync Client Event Hooks
- sp_hook_dbmlsync_dow
nload_log_ri_violati
on
and
- sp_hook_dbmlsync_dow
nload_ri_violation

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Greg Fenton

2005-10-30, 7:24 am

Greg Fenton

2005-10-30, 8:24 pm

Breck Carter [TeamSybase] wrote:
>
> You probably need to fix your download_cursor logic to make sure it's
> all ethical as far as RI is concerned.
>

And/or download_delete_curs
or logic.

If you want to download a parent record to be deleted and you don't want
to lose the associated child records, you need to download updates to
those child records (setting the FK to NULL or to another parent, as is
appropriate).

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

David Fishburn

2005-10-31, 9:24 am

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
news:0u8am1tgve37v9c
72hd8c2t1dnncbq5271@
4ax.com of
sybase.public.sqlanywhere.mobilink:

BC> I have never met any client who thinks "silent delete" is a good
BC> idea, but iAnywhere swears those folks exist :)

Yes, I am "one" of "those" people.

I use this routinely.

If I have a schema something like this:
parent table -> child -> child -> child -> child

Then if I want to delete 1 parent row and have all the children
_automatically_ deleted for me, then I only have to write ONE
download_delete_curs
or to remove the parent row.

Otherwise I would have to write download_delete_curs
or's for each and
every table. This is time consuming, database intensive, communications
additional load and NOT NECESSARY.

That is why I really like this feature.

I understand it can cause problems "debugging" in some situations (that
you would find in development mode). This is the reason we added the
sp_hook_dbmlsync_dow
nload_log_ri_violati
on

See:
MobiLink Clients
Dbmlsync Client Event Hooks
sp_hook_dbmlsync_dow
nload_log_ri_violati
on

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Breck Carter [TeamSybase]

2005-10-31, 9:24 am

On 31 Oct 2005 05:29:15 -0700, David Fishburn
<fishburn_spam@off.ianywhere.com> wrote:

>Yes, I am "one" of "those" people.

Noooo, you're not a *client* :)

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Greg Fenton

2005-10-31, 11:24 am

Breck Carter [TeamSybase] wrote:
>
> Noooo, you're not a *client* :)
>

We don't work for engineering....we're clients too...some of the more
vocal ones [present company excluded] :-)

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Carl Kayser

2005-10-31, 1:23 pm

I fall into the category of "those other people" and I agree with David
Fishburn. (Usually I am very "do it right and do it rigid" but this is an
exception.) We're starting to convert from SQL Remote to MobiLink. It's
very early in the game and it looks like we also prefer shadow tables to
logical deletes - another one of his strong preferences. Finally, we use
embedded user IDs in SQL Remote PKs and plan the same for MobiLink (but
nothing is set in concrete). Time and testing will tell.

I find it to be quite interesting in that there are different and strongly
held opinions on these issues (uniqueness, deletes) by Domaratzki, Fishburn,
Waywell, etc. as well as ASA consultants.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:baccm1dhik32ki0
22c0b0c1m0cbs9a7uai@
4ax.com...
> On 31 Oct 2005 05:29:15 -0700, David Fishburn
> <fishburn_spam@off.ianywhere.com> wrote:
>
>
> Noooo, you're not a *client* :)
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

David Fishburn

2005-11-01, 9:23 am

"Carl Kayser" <kayser_c@bls.gov> wrote in news:43666f57$1@foru
ms-1-dub
of sybase.public.sqlanywhere.mobilink:

CK> I find it to be quite interesting in that there are different and
CK> strongly held opinions on these issues (uniqueness, deletes) by
CK> Domaratzki, Fishburn, Waywell, etc. as well as ASA consultants.

Well you know how it goes, all the people who disagree with me are
wrong!! :-)

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Sponsored Links