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.
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.