mysqlsqlcreate-tablecomposite-primary-keycomposite-key

How to create a table such that when ever a record is inserted, composite key values gets inserted in another column?


I could find many methods to create a unique composite key while creating a table. But I was unable to figure out how i can create a column that contains composite key values automatically whenever a record is inserted.

For example.

if I

insert ('pine','apple') into Fruits

I want the table to be

name1   name2   fullname
------------------------
pine    apple   pine apple

my create statement.

create table ( column_names,
primary key(name1,name2));

What changes do i have to make to my create statement to achieve this ? Thanks in advance.


Solution

  • Use generated column:

    CREATE TABLE mytable ( name1 VARCHAR(127) NOT NULL,
                           name2 VARCHAR(127) NOT NULL,
                           fullname VARCHAR(255) AS (CONCAT(name1, ' ', name2)) STORED,
                           PRIMARY KEY(fullname) );
    -- or
    CREATE TABLE mytable ( name1 VARCHAR(127) NOT NULL,
                           name2 VARCHAR(127) NOT NULL,
                           fullname VARCHAR(255) AS (CONCAT(name1, ' ', name2)) VIRTUAL,
                           PRIMARY KEY(name1, name2) );
    

    Remember - you cannot set the value to generated column explicitly (in INSERT or UPDATE). Either skip it from columns list or use DEFAULT keyword for its value.