How to extract distinct combinations of columns to a new table and assign a primary key and foreign key properly

by

So I have this dataset I’m working on (SuperStore) and some of the columns I have (2 out of 17) on the main table Order_Table are :

Product_ID and Product_Name.

What I want to accomplish is :

  1. Extract all distinct combinations of these 2 columns (they are not unique) to a new table which I managed to do (I think) :
CREATE TABLE Product_Table AS
SELECT DISTINCT PRODUCT_ID,PRODUCT_NAME
FROM Order_Table
  1. Creating a new column for the Product_Table that serves as a primary key(since I don’t want to make a composite primary key.And the values goes from 1 to X) that will reference another new column in Order_Table that serves as foreign key (and this way I can drop both Product_ID and Product_Name from Order_Table ).

However,how do I go about this one ? Creating a new column for the primary key in the Product_Table should be easy.But how do I assign the proper key to each row in the Order_Table ?

Leave a Comment