Hi, I am new to database. I started creating database using libreoffice base. I have fields namely, sid and mid. Both sid and mid are primary keys. mid to be auto incremented from 1 to 3 and then reset to 1 again. sid to be autoincremented only when mid reset to 1. How can I achieve this task?
Never heard of such a construction. If you are new to database: Better describe what you want to get, not the way you try to get it, which can’t be a way with autoincrement of any of the internal databases in Base and has to be constructed by macros.
Avoid composite keys.
.
I have transfered one old archive of dbase files without changing columns to a sqlite-database. This works in sqlite, I can change values with sqlite-Studio, but Base can not change any values in this database.
.
I didn’t check, if this is a problem in Base or the odbc-driver, but I avoid this design now for Base. @keme1 showed already a way to derive your values from a simple autoincrement index.
Depending on your situation/purpose, calculated fields may work for you. Use a single key field, integer autoincrement, the “counter”. Initial value 0.
Calculated fields:
mid = counter MOD 3 + 1
sid = counter DIV 3 + 1
The DIV MOD construct needs to work with “zero base”, but I assume you need natural numbers (1 base) for both enumeration fields. This is the reason for the zero initial value and the “+1” in the formulas.