Re: [libreoffice-users] Re: Base Relationships: Update cascade not working

Hi Andreas,

Thanks for your detailed reply.  After I had sent my message, I realised
that I had neglected to mention that I am using the H2 database.

I went back into Tools | Relationships and decided to remove the the
relationship lines so that I could manually make the changes I wanted.  I
discovered that for two of the relationships it took three attempts to
delete the lines.  That made me wonder if I had somehow managed to link my
tables three times!  So I reinstated the relationships using the "New
Relation" button rather than dragging the lines.  Then, to my surprise, I
discovered that "Update cascade" worked!!

If there were three lines joining the tables, I've no idea how I managed to
do that.  Otherwise, I'm at a loss to know what I was doing wrong
yesterday.  Perhaps the overnight reboot of my PC fixed it!

Anyway, thanks again.

Noel
--
Marion & Noel Lodge
lodgemn@gmail.com

On 1 November 2014 02:53, Andreas Säger <villeroy@t-online.de> wrote:

> Am 31.10.2014 um 14:11 schrieb Marion & Noel Lodge:
> > Hi,
> >
> > I am trying to change some Primary Keys in my main table, and get the
> > changes to be reflected in several Foreign Keys.
> > I have gone into Tools |
> > ​ ​
> > Relationships, have set up the 1 to n connections between the tables and
> > ​chosen "Update cascade".
> >
> > However when I open the main table and attempt to change a Primary Key, I
> > get the message "Referential integrity constrain violation".  I thought
> > that "Update cascade" would override this.  Have I missed something, or
> is
> > it a possible bug?
> >
> > Thanks,
> >
> > Noel
> > ​
> > --
> > Noel Lodge
> > lodgemn@gmail.com
>
> Hi,
>
> If it were a bug, then it would be a bug in the underlying database
> program which is HSQL and not Base. Base is not a database at all.
>
> Referencial integrity never lets you enter any foreign key value on the
> n-side if there is no corresponding value in the other table's primary
> key (the 1-side).
> "Update cascade" allows you to update a primary key value on the 1-side.
> This update will automatically change all the values in related foreign
> keys. Normally you don't want cascading updates, particularly when using
> auto-IDs which may be inserted and deleted but never updated.
>
> If you want to enter arbitrary values into some field then you must not
> make it a foreign key of some other table's primary key. You can make it
> optional (nullable) but an orphaned foreign key value is not possible.
>
> If you happen to work with an embedded HSQLDB (this is indicated in the
> status bar), http://www.hsqldb.org/doc/1.8/guide/ch09.html is the
> complete official documentation for the database program you are working
> with.
>
> Hope this helps,
> A.S.
>
>
>
> --
> To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted