CODEWITHSUNDEEP
mysqlsql
g.gomes95
g.gomes95

Reputation: 13

Can´t create a column age from a column birthday_year in mysql

so I have a simple create table animal, for which I pretend to fill the age column by inserting only the birtday_year. I tried de getdate(), but can´t use a non-deterministic function, and even with other options, I end up with the simple one(from my point of view). But i can´t accomplish the requirements of this task.

Here is the code and one of the error:

create table animal
    ( nome varchar(255),
    VAT integer,
    species_name varchar(255),
     color varchar(255),
     gender varchar(255),
     birth_year integer,
     age generated always as ( 2018 - birth_year),
     primary key (nome,VAT),
     foreign key (VAT) references cliente(VAT) ,
     foreign key (species_name) references species(nome) );

---Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'generated always as ( 2018 - birth_year), primary key (nome,VAT), foreign ke' at line 8

I tried also:

create table animal
( nome varchar(255),
VAT integer,
species_name varchar(255),
 color varchar(255),
 gender varchar(255),
 birth_year integer,
 age  integer as ( 2018 - birth_year),
 primary key (nome,VAT),
 foreign key (VAT) references cliente(VAT) ,
 foreign key (species_name) references species(nome) );

--Error Code: 3105. The value specified for generated column 'age' in table 'animal' is not allowed.

Thanks for the help. Best regards.

Upvotes: 0

Views: 808

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

In MySQL, you need to specify the type:

create table animal (
     nome varchar(255),
     VAT integer,
     species_name varchar(255),
     color varchar(255),
     gender varchar(255),
     birth_year integer,
     age int generated always as ( 2018 - birth_year),
     primary key (nome,VAT),
     foreign key (VAT) references cliente(VAT) ,
     foreign key (species_name) references species(nome)
);

Your version with:

age  integer as ( 2018 - birth_year),

Should also work, assuming your version of MySQL supports generated columns.

Note that the definition of age is rather specific. Ideally, you would want:

year(curdate()) - birth_year

or something like that.

MySQL does not allow a volatile function such as curdate() to be used for a generated column. To implement this functionality, you need to use a view:

create table v_animal as
    select a.*, (year(curdate()) - birth_year) as age
    from animal;

Upvotes: 1

Related Questions