1

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.
3
  • 1
    age? Are you going to update every row every year?
    – Rick James
    Commented May 11, 2020 at 4:34
  • gone away is a different problem
    – danblack
    Commented May 11, 2020 at 8:24
  • No, I not thought about it Commented May 11, 2020 at 10:55

1 Answer 1

0

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
);
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.