Is there some way to have an INSERT into one table always also INSERT into another table in SQL?

I have two tables that are related to one another. For simplicity let’s define them as products and productVariants:

CREATE TABLE products (
  id STRING(64) NOT NULL,
  name STRING(64) NOT NULL,
  price FLOAT64 NOT NULL,
) PRIMARY KEY(id);

CREATE TABLE productVariants (
  variantId INT64 NOT NULL,
  name STRING(64) NOT NULL,
  parentProductId STRING(64) NOT NULL,
  childProductId STRING(64) NOT NULL,
  FOREIGN KEY(parentProductId, childProductId) REFERENCES products (id, id)
) PRIMARY KEY(variantId);

I would like the functionality that whenever a row is inserted into productVariants, a new row is inserted into products with id equal to the childProductId field in productVariants.

For example, say my products table has one row in it as follows:

id	name	price
1	Shirt	19.99

If I insert the rows into productVariants as:

variantId	name	parentProductId	childProductId
1	Red	1	1_1
2	Blue	1	1_2

(Here, childProductId is auto-generated as parentProductId_variantId)

I want new products to be inserted, leaving the products table looking something like:

id	name	price
1	Shirt	19.99
1_1	Shirt (Red)	19.99
1_2	Shirt (Blue)	19.99

Is there some sort of constraint I can add to the tables when creating them that enforces this functionality? Or is this only possible by implementing it in the code of my app?

Any insight would be appreciated. Thanks in advance!

Additional info: I am using Google Cloud Spanner.

Answer

PK/FK constraints are intended to prevent orphaned child table data not help populate parent table data. In the reverse, it won’t know what data should go into a child table when a new rows are added to the parent so there’s nothing to even check for when inserts happen on the parent table.

This type of logic is typically handled in the app layer giving users a bit of flexibility in how the child table(s) get populated. E.g. All inserted/updated in one transaction (risk of scalability issues), different values for different child rows, deliberate delay between parent and child tables to allow additional validation or augmentation in parent data that might affect child, etc…

In simpler scenarios, it is sometimes implemented using triggers. The trigger will have whatever logic needed to decide what to populate the child table for each INSERT or UPDATE on the parent. Works great for simple logic and low INSERT/UPDATE rate. Can become a measurable load on the system if your trigger is complex and/or you have sustained high volume of INSERT/UPDATE. That said, Cloud Spanner currently doesn’t support triggers.