0

So I'm working on a MySQL database with three tables:

Table 1 defines an fruit, with columns like "name", "color", "flavor" and "Fruit_ID".

Table 2 defines a category, with columns like "name", "description", and "Cat_ID".

Table 3 defines the relationship between fruit and category, with two columns, "Fruit_ID" and "Cat_ID".

Whenever a fruit is inserted, it must be correlated to a category using Table 3. My original plan was to make Fruit_ID and Cat_ID autoincrement and use this method, but the problem is my architecture has multiple users sharing just one client connection, and LAST_INSERT_ID is only guaranteed to be correct for each client, afaik.

So my question is, what kind of SQL Query can I use to ensure that when a fruit is entered into Table 1, the correct Fruit_ID is used in Table 2 to categorize it?

Also, I'm open to restructuring my database if this isn't the best way to make this kind of association (I'm new to database architecture).

I'm not open to using stored procedures in SQL.

Thanks a lot in advance!

NineBerry
  • 26,306
  • 3
  • 62
  • 93
boxcartenant
  • 271
  • 2
  • 14
  • For the record, my database isn't actually about fruit, but fruit is such an easy example to use for this kind of thing... – boxcartenant Oct 29 '18 at 23:55
  • What do you mean when you say that you have multiple users sharing just one client connection? – NineBerry Oct 30 '18 at 00:12
  • The queries will be made from Inductive Automation Ignition clients, which actually send the query text to a gateway with a pool of shared connections; there's no knowing which connection will be used by a given query. So technically, my question was inaccurately worded, but still there must be a solution for this kind of problem out there, I suspect. – boxcartenant Oct 30 '18 at 00:13
  • 1
    Does this system allow sending multiple statements as one text? If yes, there is no problem. Just send the insert and the select LAST_INSERT_ID() as one compound statement. – NineBerry Oct 30 '18 at 00:20
  • So your statements are not running in a transaction? – Jacob Oct 30 '18 at 03:01
  • I don't understand the problem with using last_insert_id – Strawberry Oct 30 '18 at 05:03
  • @NineBerry I can set up transactions, but maybe I don't understand the significance of multiple statements in a single transaction. In general, unless explicitly specified, transactions are automatically managed at the gateway. – boxcartenant Oct 30 '18 at 16:42
  • I'll research the transaction option some more; thanks! – boxcartenant Oct 30 '18 at 16:54

1 Answers1

1

If you can't use LAST_INSERT_ID(), you'll have to use a query to get the ID of the item you just inserted.

So to add a row that says apples are red, you do:

INSERT INTO fruit_category (fruit_id, cat_id)
SELECT f.id, c.id
FROM fruit AS f
CROSS JOIN category AS c
WHERE f.name = 'apple'
AND c.name = 'red'

This is the same thing you would do if you were creating this relationship and hadn't just added the rows.

Barmar
  • 741,623
  • 53
  • 500
  • 612