CODEWITHSUNDEEP
mysqlmariadb
Daniele Sartori
Daniele Sartori

Reputation: 1703

Created table with generated columns throw an error

I am trying to build my table in MySQL following this answer. Hower i keep getting errors in MySQL even launching the same query used as example in the answer

this query is from documentation and works correctly

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) stored
);

this query comes from the answer i've linked and it gives me an error

CREATE TABLE IF NOT EXISTS MyTable (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  description varchar(50) NOT NULL,
  slug text NOT NULL AS (SHA2(CONCAT(name, description), 256) STORED,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

this is the query i am trying to execute based on the answer which is giving me also an error

CREATE TABLE IF NOT EXISTS myTable(
  id BIGINT NOT NULL AUTO_INCREMENT,  
  first VARCHAR(104) NOT NULL DEFAULT '',  
  second DATETIME NULL,
  third DATETIME NULL,
  fourth VARCHAR(255) NULL,
  table_key varchar(64) NOT NULL AS (SHA2(concat_ws(',',first, second, third, fourth), 256)) stored unique,
  PRIMARY KEY (id),
  INDEX (first));

can you help me figuring out why it's not working?

Note that SHA2(concat_ws(',',first, second, third, fourth), 256) this works in a normal select statement

EDIT

this is the error i am getting

Query execution failed

Reason:

Errore SQL [1064] [42000]: (conn:28) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS (SHA2(CONCAT(name, description), 256) STORED,

  PRIMARY KEY (id)

) DEFAULT ' at line 5
Query is : CREATE TABLE IF NOT EXISTS MyTable (

  id int NOT NULL AUTO_INCREMENT,

  name varchar(50) NOT NULL,

  description varchar(50) NOT NULL,

  slug text NOT NULL AS (SHA2(CONCAT(name, description), 256) STORED,

  PRIMARY KEY (id)

) DEFAULT CHARSET=utf8

Upvotes: 1

Views: 555

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You have to add the NOT NULL after the AS part (computed column definition) of the column like this:

CREATE TABLE IF NOT EXISTS myTable(
  id BIGINT NOT NULL AUTO_INCREMENT,  
  first VARCHAR(104) NOT NULL DEFAULT '',  
  second DATETIME NULL,
  third DATETIME NULL,
  fourth VARCHAR(255) NULL,
  table_key varchar(64) AS (SHA2(concat_ws(',',first, second, third, fourth), 256)) stored unique NOT NULL,
  PRIMARY KEY (id),
  INDEX (first)
);

Also have a look at the official document of CREATE TABLE. There you can find the description of the datatype syntax. The definition of the computed column is part of the datatype.

Upvotes: 2

Related Questions