I want to automatically generate unique id in sql

I'm creating a Foods table where i can automatically generate an id for every foods I insert in the table.

create table Foods (
    (ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    ItemId AS 'itemId' + RIGHT('00000000' + CAST (ID AS VARCHAR(8)), 8) PERSISTED,
    item_name varchar(10),
    price int
);

i want the table to be created and if i give an id (i.e) 1 it should create an id as itemId00000001 . But I'm getting an error as, ERROR 1064 (42000): 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 '(ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,ItemId AS 'itemId' + RIGHT(' at line 1

Solution

From the MySQL documentation, only the NDB storage engine supports computed columns. My guess is that you are using the default storage engine InnoDB, so you would not be able to use computed columns.

In addition, you seem to be mixing SQL Server and MySQL syntax.

Taking both of these into account, I suggest using the following create statement, and then building a view on top of your table which generates the item ID you want:

CREATE TABLE Foods (
    ID int NOT NULL AUTO_INCREMENT,
    item_name varchar(10),
    price int,
    PRIMARY KEY(ID)
);

Then, create a view which can turn out the computed items you want:

CREATE VIEW Foods_View AS (
    SELECT
        ID,
        RIGHT(CONCAT('00000000', CAST(ID AS CHAR(8))), 8) AS ItemId,
        item_name,
        price
    FROM Foods
)