Base Relationships: Update cascade not working

users
#1

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
​

#2

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.

#3

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

#4

Learning a little bit of SQL you can save a lot of time and effort. The
things you need to know learn about creation and modification of tables,
fields and indexes are very simple. There are text editors which help
you to compose statements from prepared snippets. Well, even a Writer
template can be spiced up with some auto-text in SQL language.
Even the most simple plain text editor lets me define a table with
primary key, default values and foreign key faster than the Base GUI.
Back to Base, you have to call menu:View>Refresh Tables. And yes,
sometimes it is a good idea to restart the office suite. Using an
external database such as H2 keeps your data safe and warm.