USE OLTP
--Create sales_with_fk
SELECT *
INTO sales_with_fk
FROM sales

--sales_with_fk table
SELECT * FROM sales_with_fk

--create order_method_type_table
CREATE TABLE order_method_type(
order_method_type nvarchar(255))

--Populate with method types from sales
INSERT INTO order_method_type
SELECT distinct order_method_type 
FROM sales_with_fk

--Query order_method_type
SELECT * 
FROM order_method_type

--Try creating FOREIGN KEY
ALTER TABLE sales_with_fk ADD CONSTRAINT order_method_fk FOREIGN KEY (order_method_type)
                                 REFERENCES order_method_type(order_method_type)

--Create primary key first
go
ALTER TABLE order_method_type
    ALTER COLUMN order_method_type nvarchar(255) NOT NULL
go

ALTER TABLE order_method_type
    ADD CONSTRAINT order_method_pk PRIMARY KEY (order_method_type)
go

--Test
INSERT INTO order_method_type (order_method_type)
VALUES(null) --can't be null

INSERT INTO order_method_type (order_method_type)
VALUES('E-mail') --already exists

--Add foreign key
ALTER TABLE sales_with_fk ADD CONSTRAINT order_method_fk FOREIGN KEY (order_method_type)
                              REFERENCES order_method_type(order_method_type)

--Try to enter invalid data
INSERT INTO sales_with_fk (order_method_type)
VALUES('Email')

--Try to drop the order_method_type table
DROP TABLE order_method_type

--Drop the foreign key
ALTER TABLE sales_with_fk DROP CONSTRAINT order_method_fk
--Drop the table
DROP TABLE order_method_type
--Recreate it with auto-generated artificial primary key
CREATE TABLE order_method_type
(id int not null primary key identity
 ,order_method_type nvarchar(255))

--Insert order_method_types 
INSERT INTO order_method_type
SELECT distinct order_method_type 
FROM sales_with_fk

SELECT * FROM order_method_type

--Add foreign key
ALTER TABLE sales_with_fk
ADD method_type_id int FOREIGN KEY REFERENCES order_method_type(id)

--Test
INSERT INTO sales_with_fk (method_type_id)
VALUES (5)

SELECT * FROM sales_with_fk
WHERE method_type_id=5

--Update all sales_with_fk rows
UPDATE sales_with_fk
SET method_type_id = (SELECT id 
                      FROM order_method_type as mt 
					  WHERE sales_with_fk.order_method_type = mt.order_method_type)

SELECT * FROM sales_with_fk

--drop the order_method_type

ALTER TABLE sales_with_fk
DROP COLUMN order_method_type

SELECT * FROM sales_with_fk