create virtual column in mariadb with case statement fails

user_3611669 0
Viewed 191

I am trying to create a table in MariaDB with a virtual column, defined by a case statement

This is what I have

create table Foto (
  ID int AUTO_INCREMENT not null primary key,
  LigPlaatsCode varchar(10) not null,
  FotoTypeID int not null check (FotoType in (0, 1, 2, 3)),
  Foto varchar(1000) not null,
  FotoType varchar(50) as
    case FotoTypeID 
         when 0 then 'Bidprent'
         when 1 then 'Krantartikel'
         when 2 then 'Persoon'
         else 'Graf'
    end,     `

  constraint FK_Foto_LigPlaats foreign key (LigPlaatsCode) references LigPlaats (LigPlaatsCode) 
)

however it always gives me this error

#42000You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use
near 'case FotoTypeID
when 0 then 'Bidprent'
when 1 then 'Krantar' at line 7

When I google on how to create virtual column and case, the links I found seem to suggest I got it right, but obviously I dont. So I am missing something.
What is wrong with this create table statement ?

EDIT
my version is 10.3.21-MariaDB

mariadb
1 Answers

Generated column requires parantehese around case expression:

create table Foto (
  ID int AUTO_INCREMENT not null primary key,
  LigPlaatsCode varchar(10) not null,
  FotoTypeID int not null ,
  Foto varchar(1000) not null,
   FotoType varchar(50) as
    (case FotoTypeID 
         when 0 then 'Bidprent'
         when 1 then 'Krantartikel'
         when 2 then 'Persoon'
         else 'Graf'
    end), -- brackets around
  -- constraint as separate entry because it is referencing FotoType
  constraint fototypecheck check (FotoType in (0, 1, 2, 3))
);

db<>fiddle demo

This line does not make sense as FotoType is text:

FotoTypeID int not null check (FotoType in (0, 1, 2, 3)),
-- probably it should be
FotoTypeID int not null check (FotoTypeID in (0, 1, 2, 3)),
user_50708790
Related