if OBJECT_ID(N'writers', N'U') is not null
	drop table writers
go
create table writers (
	wid		int			not null,
	firstname	varchar(35)		not null,
	midname		varchar(35)		null,
	lastname	varchar(35)		not null,
	tmp		int

	PRIMARY KEY (wid)
	);
go

insert writers values
	(1, 'john', 'r.r.', 'tolkien', 1),
	(2, 'george', 'r.r.', 'martin', 2),
	(3, 'herbert', 'g.', 'wells', 3),
	(4, 'george', '', 'orwell', 4),
	(5, 'raymond', 'd.', 'bradbury', 5);
go

select * from writers;


--=====from lab7
--if OBJECT_ID(N'bookshelf_ix_view', N'V') is not null
--	drop view bookshelf_ix_view;
--go
--=====
if OBJECT_ID(N'books', N'U') is not null
	drop table books
go
create table books (
	bid	int IDENTITY(1, 1)	not null,
	name	varchar(254)		not null,
	bwid	int			not null
		CONSTRAINT DF_books_bwid DEFAULT (3),
	year	int			null,

	PRIMARY KEY (bid),
	CONSTRAINT FK_books_bwid
		FOREIGN KEY (bwid)
		REFERENCES writers(wid)
		ON DELETE SET DEFAULT
	);
go

insert into books(name, bwid, year)
	values
		('the lord of the rings', 1, 1954),
		('the hobbit, or there and back again', 1, 1937),
		('a song of ice and fire', 2, 1996),
		('1984', 4, 1949),
		('fahrenheit 451', 5, 1953);
go

select * from books;


/*
	NO ACTION -> Error
	SET DEAFULT -> set VALID deafult
	SET NULL -> -||- (CARE OF "NOT_NULL" value in books.bwid)
	CASCADE (del) -> -||-
*/

--delete from writers where writers.tmp = 1;
--go

select * from writers;
select * from books;