SQL: Create foreign table and key from a single column

Question

I've got a table with multiple duplicated entries in a column and I want to put these entries in a new table and connect these tables with a foreign key in the initial table.

Old Table:

table 1
| id | name | medium |
|  0 | xy   |  a     |
|  1 | xz   |  b     |
|  2 | yz   |  a     |

new Table:

table 1                               table2
| id | name | medium |               | id | name |
|  0 | xy   |  0     |               | 0  | a    |
|  1 | xz   |  1     |               | 1  | b    |
|  2 | yz   |  0     |

With CREATE ... SELECT I have a good tool to create a new table from the results of a query but I don't know how to change the entries from table1.medium to a foreign key based on the comparison to table2.medium. Is there any chance to do that?

Gordon Linoff · Accepted Answer · 2020-07-20T16:13:29.347

You can do:

-- Create the second table
create table table2 (
    id int primary key,
    name varchar(255) unique
);

-- Insert rows into it
insert into table2 
    select row_number() over (order by medium), medium
    from original
    group by medium;

-- Use JOIN to create the first table and populate with the right ids
create table table1 as 
    select o.id, o.name, t2.id as medium
    from original o join
         table2 t2
         on t2.medium = o.name;

Here is a db<>fiddle.

You can turn the id in table2 then into an auto_increment column:

alter table table2 modify column id int auto_increment;

ismetguzelgun · Answer 2 · 2020-07-20T16:23:58.423

If you are running mysql version 8.0 or above you can do below and use window function ROW_NUMBER.

Source

CREATE TABLE TABLE2
AS
SELECT ID,NAME(
SELECT ID, MEDIUM AS NAME,ROW_NUMBER()OVER(PARTITION BY MEDIUM ORDER BY ID) AS ROWW FROM TABLE1)
WHERE ROWW=1;

-- Adds a new column into your TABLE1 for MEDIUM's int values
ALTER TABLE TABLE1 ADD COLUMN MEDIUMINT INT;

-- Update MEDIUMINT according to your TABLE2 values.
UPDATE TABLE1 S1
JOIN TABLE2 S2 ON TABLE1.MEDIUM=S2.NAME
SET S1.MEDIUMINT=S2.ID;

-- Drops MEDIUM column from TABLE1
ALTER TABLE TABLE1 DROP COLUMN MEDIUM;

-- Rename MEDIUMINT column to MEDIUM
ALTER TABLE TABLE1 RENAME COLUMN MEDIUMINT TO MEDIUM;

score 1 · Answer 3 · answered Jul 20 '20 at 16:02

It sounds like you can create Table2 but are having trouble changing Table1, updating the column type from char to int and creating a foreign key.

Easiest would be to rename Table1 to Table3 then create Table1 as you want, insert the data, then drop Table3.

If you need to modify the existing table, it will be a multi-step process.

Add new column 'medium_old' and copy existing values from medium
Drop the column 'medium' with the char type
Add the column 'medium' with the new int type
Update the new 'medium' values to the key from Table2 based on values in 'medium_old'
Add the foreign key constraint to 'medium'

score 1 · Answer 4 · answered Jul 20 '20 at 16:06

There are two ways of doing it:

You recreate table1 with the new values in the medium column. Then you drop the old table1 and rename the new table. I would use this approach if medium column not only needs new values, but also needs to be converted to a new data type - this seems to be the case in the question. You already figured this approach out with create table ... as ...
Using multi-table update syntax, you can easily update a field in a table based on a field in another table. I would use this approach if you do not need to change the data type of the medium column. See this SO question on how to do this update.

SQL: Create foreign table and key from a single column

4 Answers4