[Solved] Autoincrement part of composite key

[xkanuk]

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

[UnklDonald418]

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

[xkanuk]

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

[Villeroy]

menu:Tools>SQL...

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)
);

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 .

[UnklDonald418]

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

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.

[xkanuk]

Thanks folks. Your advice is most appreciated!

[Villeroy]

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