use paramsan_test;
GO

-- Drop tables if necessary (if these are not tables then this will croak
-- which means there is a bigger problem)
IF OBJECT_ID('Written_By', 'TABLE') IS NOT NULL	DROP TABLE Written_By;
GO
IF OBJECT_ID('Author', 'TABLE') IS NOT NULL DROP TABLE Author;
GO
IF OBJECT_ID('Author_Auto_Index', 'TABLE') IS NOT NULL DROP TABLE Author_Auto_Index;
GO
IF OBJECT_ID('Book', 'TABLE') IS NOT NULL DROP TABLE Book;
GO

-- Create all the tables
CREATE TABLE Author (
	aid INT PRIMARY KEY ,
	name VARCHAR(20),
	age INT 
);

-- Create the author table where the aid 
--  will automatically increment (so you
--  don't have to specify it)
CREATE TABLE Author_Auto_Index (
	aid INT PRIMARY KEY IDENTITY(0, 1) ,
	name VARCHAR(20),
	age INT 
);

CREATE TABLE Book (
	bid INT PRIMARY KEY,
	title VARCHAR(50),
);

-- Written_by table has foreign key constraints
CREATE TABLE Written_by (
	bid INT REFERENCES Author,
	aid INT REFERENCES Book
);

/*
-- If cascade deletes are needed
--  can also specify ON UPDATE
--  default is to reject
CREATE TABLE Written_by (
	bid INT REFERENCES Author ON DELETE CASCADE,
	aid INT REFERENCES Book
);
*/

-- Selects
SELECT * FROM Author
SELECT * FROM Book
SELECT * FROM Written_by
SELECT * FROM Author_Auto_Index

-- Insert simple values
INSERT INTO Author VALUES (1, 'hello', 30);

-- Insert using SELECT
INSERT INTO Author SELECT a.id, a.fname, a.id FROM imdb.dbo.Actor a WHERE id = 2

-- Author.name and Author.age defaults to NULL
INSERT INTO Author(aid) VALUES (4);

-- Auto Increment
INSERT INTO Author_Auto_Index VALUES ('BLAH', 20)

-- Two possible ways to get the index corresponding to 
--  the last inserted value, which might or might not
--  work for "SELECT aid FROM Author WHERE name = 'BLAH' AND age = 20)"
--  b/c there can be mutiple values corresponding to that (name,age)
--  Also should run this as one transaction to avoid concurrency issues
SELECT IDENT_CURRENT('Author_Auto_Index')
SELECT @@IDENTITY

-- Won't be able to add custom values
INSERT INTO Author_Auto_Index VALUES (2, 'BLAH', 20)

-- This should croak b/c of fk constraint
INSERT INTO Written_by VALUES (1, 1)

-- Insert some trivial values
INSERT INTO Book VALUES (1, 'sweetness')

-- Another insert
INSERT INTO AUTHOR VALUES(312,'Michael Chabon',45); 


-- UPDATE 
-- Can do a select query for age as long as it returns
-- one row and one column with data type similar to that
-- of age
UPDATE Author SET age = (select 43) WHERE aid = 1

-- Equivalent to:
UPDATE Author SET age = 43 WHERE aid = 2

-- Delete
DELETE FROM Author WHERE aid = 2

-- Croaks b/c of FK constraint
DELETE FROM Author WHERE aid = 1

-- Don't forget the where clause
-- OTHERWISE THIS DELETES THE WHOLE TABLE CONTENTS
DELETE FROM Written_by

