DROP TABLE Knihy;
DROP TABLE Vydavatele;

-- tabulka vydavatelu knih s pojmenovanym primarnim klicem vydavatel_id a
-- jmenem vydavatele, ktere musi byt vyplneno a zadano velkymi pismeny 
CREATE TABLE Vydavatele (
	vydavatel_id NUMBER CONSTRAINT pk_vydavatelid PRIMARY KEY,
	vydavatel_jmeno VARCHAR2(50) CONSTRAINT vydavatel_upper CHECK(vydavatel_jmeno = UPPER(vydavatel_jmeno))
			             CONSTRAINT vydavatel_notnull NOT NULL
				     CONSTRAINT vydavatel_uniq UNIQUE 
);


-- zobrazeni vytvorenych omezeni 
SELECT table_name, constraint_name, constraint_type FROM user_constraints
WHERE UPPER(table_name) = 'VYDAVATELE';


-- vlozeni legalniho zaznamu do tabulky Vydavatele
INSERT INTO Vydavatele (vydavatel_id, vydavatel_jmeno) VALUES (1, 'GRADA');


-- chyba: vlozeni zaznamu bez vyplneneho jmena 
INSERT INTO Vydavatele (vydavatel_id) VALUES (3);

-- chyba: vlozeni zaznamu bez velkych pismen 
INSERT INTO Vydavatele (vydavatel_id, vydavatel_jmeno) VALUES (2, 'Marecek');

-- chyba: vlozeni neunikatniho zaznamu
INSERT INTO Vydavatele (vydavatel_id, vydavatel_jmeno) VALUES (2, 'GRADA');


-- kniha_id je primarni klic. constraint pojmenovan jako pk_bookid;
-- kniha_jmeno musi byt vyplnen a unikatni. constraint neni pojmenovan;
-- nakladatel musi byt vyplnen. Prodej zahajen dle 'start_prodeje', pokud
-- neni pri vkladani vyplneno, pouzije se aktualni datum */
CREATE TABLE Knihy (
	kniha_id NUMBER CONSTRAINT pk_bookid PRIMARY KEY,
	kniha_jmeno VARCHAR2(30) NOT NULL UNIQUE,
	vydavatel_id NUMBER NOT NULL,        
	start_prodeje DATE DEFAULT SYSDATE
);

-- zobrazi informace o vytvorenych integritnich omezenich vcetne jeho typu
-- ze systemove tabulky 'user_constraints' 
-- (dalsi zajimava tabulka je 'user_cons_columns') 
SELECT table_name, constraint_name, constraint_type FROM user_constraints WHERE
UPPER(table_name) = 'KNIHY';

-- pridame pozadavek na vyplneni polozky isbn a jeji unikatnost 
ALTER TABLE Knihy ADD CONSTRAINT uniq_isbn isbn VARCHAR2(15) UNIQUE NOT NULL;

-- pridame referenci na cizi klic z tabulky Vydavatele na polozku
-- Vydavatele.vydavatel_id. od teto chvile bude kontrolovan vyskyt hodnoty
-- Knihy.vydavatel_id v tabulce Vydavatele a tabulka Vydavatele pujde
-- odstranit az po odstraneni tabulky Knihy (ktera ji referencuje)  
ALTER TABLE Knihy ADD CONSTRAINT fk_publishid FOREIGN KEY (vydavatel_id)
REFERENCES Vydavatele(vydavatel_id);

SELECT table_name, constraint_name, constraint_type FROM user_constraints
WHERE UPPER(table_name) = 'KNIHY';

-- chyba: nelze odstranit tabulku, na kterou je uvedena reference pres FOREIGN KEY
DROP TABLE Vydavatele;

-- vlozeni legalniho zaznamu do tabulky Knihy 
INSERT INTO Knihy (kniha_id, kniha_jmeno, isbn, vydavatel_id) VALUES (1,
'Citadela', '80-5246545-321', 1);

-- chyba: nelze vlozit zaznam do Knih pro neexistujiciho vydavatele 
INSERT INTO Knihy (kniha_id, kniha_jmeno, isbn, vydavatel_id) VALUES (2,
'1984', '80-032453434-12', 3);

-- chyba: nelze odstranit vydavatele, ktery je referencovan zaznamen z tabulky
-- Knihy  
DELETE FROM Vydavatele WHERE vydavatel_id = 1;

SELECT table_name, constraint_name, constraint_type FROM user_constraints
WHERE UPPER(table_name) = 'VYDAVATELE';

-- odstraneni omezeni na unikatnost jmena vydavatele
ALTER TABLE VYdavatele DROP CONSTRAINT vydavatel_uniq;

SELECT table_name, constraint_name, constraint_type FROM user_constraints
WHERE UPPER(table_name) = 'VYDAVATELE';

SELECT table_name, constraint_name, constraint_type FROM user_constraints
WHERE UPPER(table_name) = 'KNIHY';

-- odstraneni tabulky Vydavatele vcetne vsech souvisejicich integritnich
-- omezeni (Knihy.fk_publishid)
DROP TABLE Vydavatele CASCADE CONSTRAINTS;

SELECT table_name, constraint_name, constraint_type FROM user_constraints
WHERE UPPER(table_name) = 'KNIHY';