MySQL – Proper Way to Create Friends Table in Single Statement

MySQL

I have table users

CREATE TABLE IF NOT EXISTS users(
    id int AUTO_INCREMENT PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE,
    goal VARCHAR(255) DEFAULT NULL,
    age INTEGER DEFAULT NULL,
    gender VARCHAR(255) DEFAULT NULL,
    country VARCHAR(255) DEFAULT NULL,
    city VARCHAR(255) DEFAULT NULL,
    comment VARCHAR(255) DEFAULT NULL)

I want to create table friends. One key feature that is every user by default friend for itself.

This is my current statement

create table friends (user_id int,  friend_id int, FOREIGN KEY (user_id) REFERENCES users (user_id), foreign key (friend_id) references users (user_id)) as select user_id from users;

update friends set shown_id = user_id;

I think it looks pretty kludgy, creating a table and changing it immediately, may be there a better way to implement it?

Another important note: the database loses connection after creating the friends table.

create table friends (user_id int,  friend_id int,
        FOREIGN KEY (user_id) REFERENCES users (user_id),
        foreign key (friend_id) references users (user_id)
) as select user_id from users;
ERROR: 2006: MySQL server has gone away
The global session got disconnected..
Attempting to reconnect to 'mysqlx://root@localhost:33060/test_db'....
The global session was successfully reconnected.

Best Answer

Don't know where shown_id came from, but I guess it's a typo.

Instead of populating only one column (user_id) of your friends table, you can add another user_id column to the select statement with friend_id alias. Immediate update query is not needed.

create table friends (
    user_id int,
    friend_id int,
    foreign key (user_id) references users (user_id),
    foreign key (friend_id) references users (user_id)
) as (
    select user_id, user_id as friend_id 
    from users
);
Related Question