Hi there. Spent some two days, made my firist base database. Unfortunately I can’t add any data to my form. I start to look for possible mistakes. I did find. It is inside a table. That is the primary key, its AutoValue was set to “no”. It should be “Yes”. I tried many times but I failed. It always prompt a warning, “The column “C_ID” could not be changed. Should the column instead be deleted and the new format appended?” If I choose Yes, then it came the Error While saving the table design, saying “Column is referenced in constraint or view: SYS_REF_SYS_FK-210_229 in statement [ALTER TABLE “tbl_Client” DROP “C_ID””. It is so frustrating. Please help. 
Hello,
What database are you using - HSQLDB embedded, MySQL, MariaDB, PostgreSQL, etc?
May help to also know your OS and the specific LO version you are using.
Thanks it is HSQLDB embedded. MacOS 11.5.2, Libre Office 7.1.5. HIH.
I tried to delete that problematic table, but i was not allowed to do so. I am stucked here.
Since this appears to be a new Base file without data, please edit your original question and post a copy of the Base file. Will repair & try to give explanation on what may be the problem(s).
menu:Tools>SQL…
ALTER TABLE "table name" ALTER COLUMN "column name" INT IDENTITY
[replace the names with the actual names]
[Execute]
menu:View>Refresh Tables
Thanks, but still I get this error:
Column is referenced in constraint or view: SYS_REF_SYS_FK_210_229 in statement [ALTER TABLE “tbl_Client” ALTER COLUMN “C_ID” INT IDENTITY]
Open the relations design window, delete the relation line and save the change.
Then try the SQL statement again.
Finally, reestablish the relation.
1 Like
Man, you made my day! If it is possible I would like to know why your way solved the prolem. Many thanks bro! 
You are working with a frontend application Base which controls a backend application HSQL. They are 2 different softwares. HSQL can be used without any office suite to feed a web sit for example. Likewise, you can connect a Base document to MySQL, PostgreSQL, MS Access, Oracle, dBase files and many others.
Whatever you do in the whole Base frontend is translated into SQL commands sent to the connected database. The SQL window lets you talk directly to the backend bypassing the Base frontend. The final command menu:Tools>“Refresh Tables” tells Base to re-read the whole database after you made changes through the SQL window.
Databases are very picky when you change structures that are related to other structures. Or when you impose new rules that are violated by existing rules. In this case, where you try to change the type or mode of an existing field that is related to another field, Base should provide a more explanative error message with a solution. Instead it simply forwards the error message from the HSQL backend. I have seen similar cases where some modification failed without any relation to another table being involved and sending the command through the SQL window simply fixed the problem. Reading your error message more carefully, I see that it indicates that the error is related to a foreign key (a connection line in the relation designer).
The HSQL database you are using is embedded in the Base document. This looks pretty nice (because it looks like MS Access) but the sad reality is that your data are in danger. When you open the document and access any record set of that database, the embedded database will be “installed” to a temporary directory. When you close the record set, the whole database will be wrapped back into the document. Sooner or later the application will have some problem (crash, hibernation) during the wrapping/unwrapping and all backend data will be lost.
You should really backup your database document with the embedded backend after every session!
The long term solution to this problem is to separate the database from the document and run it as a stand-alone database. I run stand-alone HSQL databases since 11 years without a single problem.
1 Like
Wow!!! You know so much!!! Thank you so much for helping me solving this problem and explaining in such detail. You have my respect.
If you can shed some lights in how to separate the database from the document and run it as a stand-alone database that would be highly appreciated!
Meanwhile, the latest HSQL versions >= 2.5.0 can not convert HSQL 1.8 anymore. You have to connect an older version 2.3.x or 2.4.x to the extracted 1.8 database. This will convert the extracted 1.8 database on the fly. In a second step you can upgrade to a more recent HSQL version.
Of course, you always keep a backup copy of your embedded database until you successfully tested all your queries, forms and reports with the new one.
1 Like
See if [Tutorial] Splitting an “embedded HSQL database” helps.
Thank you robleyde so much. I will try this way.
Meanwhile, the latest HSQL versions >= 2.5.0 can not convert HSQL 1.8 anymore. You have to connect an older version 2.3.x or 2.4.x to the extracted 1.8 database. This will convert the extracted 1.8 database on the fly. In a second step you can upgrade to a more recent HSQL version.
Of course, you always keep a backup copy of your embedded database until you successfully tested all your queries, forms and reports with the new one.
Thank you Villeroy for your generosity for this important knowledge. Much appreciated. Will use them.