Need a better way to insert non-existent values in referenced table during insert/update in referencing one

Question

I'm looking for the best way to insert data into MariaDB with JDBC. Please take a look at the following schema

CREATE TABLE color (
  id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE ball (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  name     VARCHAR(255) NOT NULL,
  color    SMALLINT UNSIGNED,
  KEY color (color),
  CONSTRAINT color_fk FOREIGN KEY (color) REFERENCES color(id)
);

and test data

INSERT INTO color (name) VALUES ('green'), ('yellow'), ('red');
INSERT INTO ball (name,color) VALUES ('Big Yellow Ball',(SELECT id FROM color WHERE name = 'yellow')); -- OK
INSERT INTO ball (name,color) VALUES ('Small Purple Ball',(SELECT id FROM color WHERE name = 'purple')); -- DOESN'T EXIST

and as result NULL color value is inserted for Purple Ball

MariaDB [zimbra]> select * from ball;
+----+-------------------+-------+
| id | name              | color |
+----+-------------------+-------+
|  1 | Big Yellow Ball   |     7 |
|  2 | Small Purple Ball |  NULL |
+----+-------------------+-------+

Thus I can see the following solution

1. try to read required color
2. then if it doesn't exist insert a new record for it
3. then extract generated key
4. prepare and execute next query/statement with it

which doesn't look very elegant as it requires from 2 to 3 statements preparation (in Java), with further execution and results extraction for each of them. This becomes even more ugly taking into account that there are more than one such referenced tables in my real case. Another reason to optimize this is performance: to send less SQL requests and to execute less PreparedStatements).

Ideally, I would like to be able to construct such SQL insert statement which would bother about non-existent referencing values.

Otherwise, if it is impossible, what other solution I should look at which would allow me to simplify my Java code?

Answer 1

Instead of trying to insert directly into the ball table you could insert your data into a temporary newdata table ...

CREATE TEMPORARY TABLE newdata (
    name VARCHAR(255),
    colorname VARCHAR(255)
    );

INSERT INTO newdata (name, colorname) VALUES 
    ('Big Yellow Ball', 'yellow'),
    ('Small Purple Ball', 'purple');

... then perform an INSERT INTO color to create entries for any color names that don't already exist ...

INSERT INTO color (name)
SELECT colorname FROM newdata
WHERE colorname NOT IN (SELECT name FROM color);

... and then INSERT INTO ball with

INSERT INTO ball (name, color)
SELECT newdata.name, color.id
FROM newdata INNER JOIN color ON newdata.colorname = color.name;