Referential Integrity

-- Create authors table first (referenced table)
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    nationality VARCHAR(50)
);

-- Create categories table with self-reference
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_category_id INT,
    CONSTRAINT fk_category_parent 
        FOREIGN KEY (parent_category_id) 
        REFERENCES categories(category_id)
);

-- Create books table with foreign key constraints
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_qty INT DEFAULT 0,
    published_year INT,
    CONSTRAINT fk_book_author 
        FOREIGN KEY (author_id) 
        REFERENCES authors(author_id),
    CONSTRAINT fk_book_category 
        FOREIGN KEY (category_id) 
        REFERENCES categories(category_id)
);

-- First, insert valid reference data
INSERT INTO authors (author_id, first_name, last_name, nationality)
VALUES (1, 'Stephen', 'King', 'American'),
       (2, 'J.K.', 'Rowling', 'British');

INSERT INTO categories (category_id, name, parent_category_id)
VALUES (1, 'Fiction', NULL),
       (2, 'Horror', 1),
       (3, 'Fantasy', 1);

-- VIOLATION 1: INSERT with non-existent foreign key
INSERT INTO books (book_id, title, author_id, category_id, price)
VALUES (1, 'The Shining', 999, 2, 15.99);

-- INSERT valid book first
INSERT INTO books (book_id, title, author_id, category_id, price)
VALUES (1, 'The Shining', 1, 2, 15.99);

-- VIOLATION 2: UPDATE to non-existent foreign key
UPDATE books 
SET author_id = 888 
WHERE book_id = 1;

-- VIOLATION 3: DELETE referenced parent record
DELETE FROM authors WHERE author_id = 1;

-- Drop and recreate tables with cascade options
DROP TABLE IF EXISTS order_items, orders, books;

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_orderitem_order 
        FOREIGN KEY (order_id) 
        REFERENCES orders(order_id) 
        ON DELETE CASCADE,  -- Delete order items when order is deleted
    CONSTRAINT fk_orderitem_book 
        FOREIGN KEY (book_id) 
        REFERENCES books(book_id) 
        ON DELETE RESTRICT  -- Prevent book deletion if in orders
        ON UPDATE CASCADE   -- Update book_id references automatically
);

-- Insert test data
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (100, 1, '2024-01-15', 47.98);

INSERT INTO books (book_id, title, author_id, category_id, price)
VALUES (10, 'It', 1, 2, 18.99),
       (11, 'Pet Sematary', 1, 2, 16.99);

INSERT INTO order_items (order_item_id, order_id, book_id, quantity, unit_price)
VALUES (1, 100, 10, 1, 18.99),
       (2, 100, 11, 1, 16.99);

-- Check current data
SELECT 'Order Items' as table_name, COUNT(*) as count FROM order_items
UNION ALL
SELECT 'Orders', COUNT(*) FROM orders;

-- Delete the order - should cascade to order_items
DELETE FROM orders WHERE order_id = 100;

-- Check data after cascade delete
SELECT 'Order Items After Delete' as table_name, COUNT(*) as count FROM order_items
UNION ALL
SELECT 'Orders After Delete', COUNT(*) FROM orders;

-- Create tables with one nullable foreign key
CREATE TABLE authors_extended (
    author_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    featured_book_id INT NULL  -- Nullable to break circular dependency
);

CREATE TABLE books_extended (
    book_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    CONSTRAINT fk_book_author_ext 
        FOREIGN KEY (author_id) 
        REFERENCES authors_extended(author_id)
);

-- Add foreign key constraint after table creation
ALTER TABLE authors_extended 
ADD CONSTRAINT fk_author_featured_book 
    FOREIGN KEY (featured_book_id) 
    REFERENCES books_extended(book_id);

-- Two-step insert process
INSERT INTO authors_extended (author_id, first_name, last_name)
VALUES (1, 'Stephen', 'King');

INSERT INTO books_extended (book_id, title, author_id)
VALUES (100, 'The Stand', 1);

UPDATE authors_extended 
SET featured_book_id = 100 
WHERE author_id = 1;

-- MySQL syntax for disabling foreign key checks
SET FOREIGN_KEY_CHECKS = 0;

-- Run your bulk imports here
INSERT INTO books (book_id, title, author_id, category_id, price)
VALUES (1000, 'Bulk Import Book', 999, 888, 19.99);  -- Even with invalid FKs

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- Verify data integrity after import
-- This will fail because author 999 doesn't exist
UPDATE books SET title = 'Updated Title' WHERE book_id = 1000;

-- Create a comprehensive view of all foreign key relationships
SELECT 
    TABLE_NAME as child_table,
    COLUMN_NAME as foreign_key_column,
    CONSTRAINT_NAME as constraint_name,
    REFERENCED_TABLE_NAME as parent_table,
    REFERENCED_COLUMN_NAME as parent_column
FROM information_schema.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME IS NOT NULL 
    AND TABLE_SCHEMA = 'bookvault'
ORDER BY TABLE_NAME, CONSTRAINT_NAME;