How do I create another table from an existing table in MySQL?

Question

I have a table called UserInfo like this:

Userid     Job     Age
John       A       35
Sarah      B       27

and I want to create another table like this called UserScores (score is null):

Userid     Score
John       
Sarah

Where the userid column is the same exact as the other table. How would I do this with SQL? This is what I tried:

CREATE TABLE UserScores AS
    SELECT userid, score
    FROM user

Here's my error message:

I need the userid to be the primary key in my UserScores table as well. How would I do this? Thanks in advance.

user has no scores so how should mysqlm know where the score come from and for the primary alter the table — nbk, Mar 26 '21 at 21:07
Something like this? https://stackoverflow.com/questions/5575051/how-to-create-foreign-key-that-is-also-a-primary-key-in-mysql — Roger Asbury, Mar 26 '21 at 21:09
@nbk, ah well i was planning to leave the scores empty, my bad let me fix that — i am bad at coding, Mar 26 '21 at 21:10

nbk · Accepted Answer · 2021-03-26T21:29:19.433

3

You add NULL as value to scores as below and add the promary kley after creatuing the table userscores

CREATE TABLE user (userid INT)

INSERT INTO user VALUES (1)

CREATE TABLE UserScores AS
    SELECT userid, NULL AS score
    FROM user

ALTER TABLE UserScores
ADD PRIMARY KEY (userid);

db<>fiddle here

EDIt

you should also define the type of score in the create

CREATE TABLE UserScores (userid  int, scrore int) AS
    SELECT userid, NULL AS score
    FROM user

edited Mar 26 '21 at 21:29

answered Mar 26 '21 at 21:14

nbk

1

note that you will likely want to change score's data type after this. I'd just make the table the way you want, keys and all, and then `insert into UserScores (userid) select userid from user;` – ysth Mar 26 '21 at 21:23

1 Answers1