MySQL Workbench – Generated Column

database-designMySQLscripting

I am a beginner and start designing small database. I've found that to automatically generate value from the table, I can either use a trigger or compute them when forward engineering.

Therefore, I've referred to:

  1. https://stackoverflow.com/questions/5222044/column-calculated-from-another-column
  2. https://gabi.dev/2016/10/17/understanding-generated-columns/

But what I am looking for is to compute the value from two tables. Is that possible to do SELECT to grab the value for generated values? Or I've done it wrongly. Any help or hint is highly appreciated.

So far, I've tried this, but it's wrong:

CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
`OrderNumber` INT NOT NULL,
`ShippingAddress ID` INT NOT NULL,
`OrderDate` DATE NOT NULL,
`Shipping Cost` INT NOT NULL,
`Customer_ID` INT NOT NULL,
 /* tried to get the value from two table */
`ShipWeight` INT GENERATED ALWAYS AS (
 SELECT SUM(Product.ProductCode*Quantity)
 FROM Order
 INNER JOIN Product on Product.ProductCode = Order.ProductCode) VIRTUAL,
`Quantity` INT NOT NULL,

Best Answer

The short answer is you can't. The rules for the expression of the generated column are (see create-table-generated-columns.html):

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

  • Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

  • Stored functions and user-defined functions are not permitted.

  • Stored procedure and function parameters are not permitted.

  • Variables (system variables, user-defined variables, and stored program local variables) are not permitted.

  • Subqueries are not permitted.

  • A generated column definition can refer to other generated columns, but only those occurring earlier in the table definition. A generated column definition can refer to any base (nongenerated) column in the table whether its definition occurs earlier or later.

[...]

Related Question