USE OLTP

--sales table.
SELECT * FROM sales_with_pk

--Customers table
CREATE TABLE customers (
    id int not null primary key identity
	,name varchar(255))

INSERT INTO customers
SELECT 'Customer a'
UNION
SELECT 'Customer b'
UNION
SELECT 'Customer c'

SELECT * FROM customers

--create orders table
CREATE TABLE orders
(id int  PRIMARY KEY IDENTITY,
 customer_id int FOREIGN KEY REFERENCES customers(id),
 date date,
 order_method_type char(120))

--popuulate orders table
INSERT INTO orders
SELECT 1,'2016-01-01','E-Mail'
UNION 
SELECT 2,'2016-01-01','Web'
UNION 
SELECT 3,'2016-01-02','Web'
UNION 
SELECT 2,'2016-01-02','Sales Visit'

SELECT * FROM ORDERS

--Create order items table
CREATE TABLE order_items
(order_id int not null,--This is the order that this item belongs to
 item_number int not null,--This is the line number for this item
 product_code char(5),
 sale_price money,
 quantity_sold int)

 INSERT INTO order_items (order_id,item_number,product_code,sale_price,quantity_sold)
 SELECT top 20 CASE WHEN id <3 THEN 1
                    WHEN id <7 THEN 2
					WHEN id <12 THEN 3
					ELSE 4
				END as order_number
			   ,row_number() over(partition by CASE WHEN id <3 THEN 1
                    WHEN id <7 THEN 2
					WHEN id <12 THEN 3
					ELSE 4
				END order by id )
			   ,product_code,sale_price,quantity_sold
 FROM sales_with_pk
 ORDER BY id

 SELECT * FROM order_items

--Add primary key
ALTER TABLE order_items
ADD CONSTRAINT order_items_pk PRIMARY KEY (order_id,item_number)

--Test null
INSERT INTO order_items(order_id,item_number)
VALUES(1,null)

--And as a combination they have to be unique
INSERT INTO order_items(order_id,item_number)
VALUES(1,1)--not unique

INSERT INTO order_items(order_id,item_number)
VALUES(1,3)--is unique

INSERT INTO order_items(order_id,item_number)
VALUES(5,3)--is also unique

--Get rid of the offending row
SELECT * FROM order_items
DELETE FROM order_items WHERE order_id = 5
SELECT * FROM order_items

--Add the foreign key
ALTER TABLE order_items
ADD CONSTRAINT order_order_items_fk FOREIGN KEY (order_id) REFERENCES orders(id)

--Try adding an item to an order that doesn't exist
INSERT INTO order_items(order_id,item_number)
VALUES(5,3)