CODEWITHSUNDEEP
sqlmysql
user2986042
user2986042

Reputation: 1270

Error " generated Column cannot refer to auto-increment column " in MySQL Table

I am trying to create a Table in MySQL with id as bigint not null auto_increment . This is the structure of MySQL table 

create table test_profile (
  id bigint not null auto_increment,
  type integer not null,
  type2 integer generated always as (case when type = 0 then id else type end),
  primary key (id),
  constraint fk_profile_to foreign key(test_profile_1) references test_profile_1(id) on delete cascade
);

I have run this table on MySQL workbench , I am getting the following error 

Error Code: 3109. Generated column 'type2' cannot refer to auto-increment column.

Any idea to solve this type of error ? Any alternative methods or suggestions ?

Upvotes: 5

Views: 4901

Answers (2)

Luuk
Luuk

Reputation: 14929

Using a trigger is indeed the correct option, but the answer from Sushil Bohara produces: "Can't update table 'test_profile' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."

Writing it like this works without this error:

CREATE TRIGGER TRGUpdateTestProfile
    BEFORE INSERT ON test_profile
    FOR EACH ROW 
    SET new.type2 = case when new.type = 0 then new.id else new.type end;

See: DBFIDDLE

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

Use trigger instead to fulfill your requirement as generated column can't refer auto increment column . This is the one good alternative for performing such a task. Below is insert trigger and I think you have to create AFTER UPDATE trigger also

DELIMITER $$
CREATE TRIGGER TRGUpdateTestProfile
    AFTER INSERT ON test_profile
    FOR EACH ROW 
BEGIN  
    UPDATE test_profile SET Type2 = case when type = 0 then new.id else new.type end 
    WHERE id = new.id;
END$$
DELIMITER;

Upvotes: 1

Related Questions