Hi,
I am trying to build a table that will autoincrement lines for each of the first part of a composite key.
Here's my example:
I have a Recipe table with a primary key of id_Recipe
I need a details table called Recipe_Steps where the composite key would be id_Recipe and id_Step where id_Step would autoincrement from "1" for each new id_Recipe.
Is this possible without extra coding? If not, should I just go with id_Step as an autoincrement column and not worry about its actual number? Not great for when I want to print out the recipe but can deal with it later.
Thanks for listening!
..sherm
[Solved] Autoincrement part of composite key
[Solved] Autoincrement part of composite key
Last edited by xkanuk on Wed Feb 27, 2019 2:33 am, edited 1 time in total.
LibreOffice 6.0.7.3 on Mac
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Autoincrement part of composite key
It isn't clear, would "id_Step" need reset with each new recipe?
The need for a composite key raises a red flag. It would probably be better to have a Steps table to establish a one to many relationship between Recipes and Steps.
If you switch from the Embedded database to a JDBC (split) database the updated database engine has a ROWNUM() function that could be used to enumerate the steps.
[Wizard] Create a new 'split' HSQL 2.x database
The need for a composite key raises a red flag. It would probably be better to have a Steps table to establish a one to many relationship between Recipes and Steps.
If you switch from the Embedded database to a JDBC (split) database the updated database engine has a ROWNUM() function that could be used to enumerate the steps.
[Wizard] Create a new 'split' HSQL 2.x database
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Autoincrement part of composite key
The table layout is as follows:
Ingredients (key=id_Ingredient)
Recipes(key=id_Recipe)
Recipe_Ingredients(composite key = id_Recipe, id_Ingredient)
and I would like to include a Recipe_Steps table that lists the steps for the recipe. So I envision it to look like:
id_Recipe, step_number, step. eg
1,1, first step for recipe 1
1,2, second step for recipe 1
1,n, nth step for recipe 1
2,1,first step for recipe 2
2,2, second step for recipe 2
etc
Ingredients (key=id_Ingredient)
Recipes(key=id_Recipe)
Recipe_Ingredients(composite key = id_Recipe, id_Ingredient)
and I would like to include a Recipe_Steps table that lists the steps for the recipe. So I envision it to look like:
id_Recipe, step_number, step. eg
1,1, first step for recipe 1
1,2, second step for recipe 1
1,n, nth step for recipe 1
2,1,first step for recipe 2
2,2, second step for recipe 2
etc
LibreOffice 6.0.7.3 on Mac
Re: Autoincrement part of composite key
menu:Tools>SQL...
menu:View>Refresh Tables
Create a main form with receipes and a subform for the steps of a selected receipe.
Code: Select all
CREATE TABLE "Steps"(
ID INT IDENTITY,
N TINYINT,
DESCR VARCHAR_IGNORECASE(1000),
RID INT NOT NULL,
FOREIGN KEY (RID) REFERENCES Receips (ID)
);
Create a main form with receipes and a subform for the steps of a selected receipe.
Edit: Sorry, this will not auto-increment the "N" column but if you sort by ID, you will get the order of appearance without the ordinal numbers 1,2,3,... HSQL 2 has a ROWNUM function for this purpose. |
Edit: Removed NOT NULL clause from N TINYINT. See below 27 Feb 2019, 15:55 . |
Last edited by Villeroy on Wed Feb 27, 2019 4:56 pm, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Autoincrement part of composite key
Recipe_Ingredients is an intersection table and It will be better behaved with its own primary key rather than a composite of foreign keys.
To create one you could Execute this at Tools>SQL
Then select View>Refresh Tables to inform the Base front end of the changes made to the tables.
To create one you could Execute this at Tools>SQL
Code: Select all
CREATE TABLE "Recipe_Ingredients" (
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"id_Recipe" INTEGER,
"id_Ingredient" INTEGER,
CONSTRAINT "FK_id_Recipe" FOREIGN KEY ("id_Recipe") REFERENCES "Recipes" ("id_Recipe"),
CONSTRAINT "FK_id_Ingredient" FOREIGN KEY ("id_Ingredient") REFERENCES "Ingredients" ("id_Ingredient")
);
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Autoincrement part of composite key
Thanks folks. Your advice is most appreciated!
LibreOffice 6.0.7.3 on Mac
Re: [Solved] Autoincrement part of composite key
Receipes and ingredients with an intersection table "Recipe_Ingredients" is a many-to-many relation.
However, a receipe may consist of ingredients and step by step instructions.
The "Steps" table I suggested is a one-to-many relation where each step belongs to one receipe specified by the foreign key RID. Your actual names may vary but if you introduce my table, make the "N" column optional by removing the NOT NULL clause. Then you can query the right order of steps like this:
SELECT <some columns> FROM <some tables> ORDER BY "N" ASC, "ID" ASC. This will sort the steps by ID if no N is given and you may override the order by providing the numbers in column N
However, a receipe may consist of ingredients and step by step instructions.
The "Steps" table I suggested is a one-to-many relation where each step belongs to one receipe specified by the foreign key RID. Your actual names may vary but if you introduce my table, make the "N" column optional by removing the NOT NULL clause. Then you can query the right order of steps like this:
SELECT <some columns> FROM <some tables> ORDER BY "N" ASC, "ID" ASC. This will sort the steps by ID if no N is given and you may override the order by providing the numbers in column N
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice