[Solved] Autoincrement part of composite key

Creating tables and queries

[Solved] Autoincrement part of composite key

Postby xkanuk » Tue Feb 26, 2019 8:38 am

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
Last edited by xkanuk on Wed Feb 27, 2019 2:33 am, edited 1 time in total.
LibreOffice 6.0.7.3 on Mac
xkanuk
 
Posts: 5
Joined: Tue Feb 26, 2019 8:28 am

Re: Autoincrement part of composite key

Postby UnklDonald418 » Tue Feb 26, 2019 5:36 pm

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
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1194
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Autoincrement part of composite key

Postby xkanuk » Tue Feb 26, 2019 10:18 pm

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
LibreOffice 6.0.7.3 on Mac
xkanuk
 
Posts: 5
Joined: Tue Feb 26, 2019 8:28 am

Re: Autoincrement part of composite key

Postby Villeroy » Tue Feb 26, 2019 10:33 pm

menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
CREATE TABLE "Steps"(
  ID INT IDENTITY,
  N TINYINT,
  DESCR VARCHAR_IGNORECASE(1000),
  RID INT NOT NULL,
  FOREIGN KEY (RID) REFERENCES Receips (ID)
);

menu:View>Refresh Tables
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Autoincrement part of composite key

Postby UnklDonald418 » Tue Feb 26, 2019 11:56 pm

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
Code: Select all   Expand viewCollapse view
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")
);

Then select View>Refresh Tables to inform the Base front end of the changes made to the tables.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1194
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Autoincrement part of composite key

Postby xkanuk » Wed Feb 27, 2019 12:49 am

Thanks folks. Your advice is most appreciated!
LibreOffice 6.0.7.3 on Mac
xkanuk
 
Posts: 5
Joined: Tue Feb 26, 2019 8:28 am

Re: [Solved] Autoincrement part of composite key

Postby Villeroy » Wed Feb 27, 2019 4:55 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 0 guests