Why i can't create this table on MYSQL Workbench? (Error on Constraint)

leunab g 0
Viewed 26

I was trying to create a table on my MySQL-Workbench, but, everytime i try to execute this line under me, the code didn't execute. It's say that is a "Error Code : 3813 Column check constraint 'lancamento_chk_2' references other column".

create table lancamento
    (
    id bigint not null primary key auto_increment,
    descricao varchar(100) not null,
    mes integer not null,
    ano integer not null, 
    valor numeric (16,2) not null,
    tipo varchar(20) check( tipo in('RECEITA','DESPESA')) not null,
    status_lanca varchar(20) check(tipo in ('PENDENTE','CANCELADO','EFETIVADO')) NOT NULL,
    id_usuario bigint references usuarios(id) ,
    data_cad_lanca date
    );
mysql-workbench mysql
1 Answers

As the error message suggest, you have a check constraint during column definition that is checking another column than itself. This is not valid in MySQL.

And since the name is lancamento_chk_2, it means this is about hte 2nd check in your table, therefore it's the line about status_lanca column, I guess the correct one should be:

status_lanca varchar(20) check(status_lanca in ('PENDENTE','CANCELADO','EFETIVADO')) NOT NULL,

(instead of check(tipo in...).

I'm guessing the error is actually the column name, since you are already checking that tipo is something else on the line above.

If you actually do want to create a check involving multiple columns, in this case you need to add an explicit constraint after the columns list.

Pac00
Related