Constraints & Indexes

CREATE TABLE courses (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL
);

CREATE TABLE enrollments (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_on DATE DEFAULT (CURRENT_DATE),

    -- FOREIGN KEY — referential integrity
    FOREIGN KEY (student_id) REFERENCES students(id)
        ON DELETE CASCADE,    -- student delete → enrollments delete
    FOREIGN KEY (course_id) REFERENCES courses(id)
        ON DELETE RESTRICT    -- course delete — enrolled असेल तर error
);

-- student_id = 999 नाही students table मध्ये
-- INSERT INTO enrollments (student_id, ...) VALUES (999, ...);
-- ❌ Error: Cannot add or update a child row

-- Index म्हणजे book च्या शेवटी index सारखे
-- Frequently searched columns वर index बनवा

-- Single column index
CREATE INDEX idx_city ON students(city);

-- Composite index — multiple columns
CREATE INDEX idx_city_marks ON students(city, marks);

-- Unique index
CREATE UNIQUE INDEX idx_email ON students(email);

-- Index वापरून query automatically fast होते
SELECT * FROM students WHERE city = 'Pune';
-- आता city = 'Pune' शोधणे खूप fast!

-- Index पाहणे
SHOW INDEX FROM students;

-- Index काढणे
DROP INDEX idx_city ON students;

-- View म्हणजे saved query — virtual table
-- Complex query एकदा define करा, नंतर table सारखे वापरा

CREATE VIEW pune_toppers AS
SELECT name, marks, city
FROM students
WHERE city = 'Pune' AND marks >= 80
ORDER BY marks DESC;

-- View वापरणे — table सारखेच!
SELECT * FROM pune_toppers;
SELECT name FROM pune_toppers WHERE marks > 85;

-- View update करणे
CREATE OR REPLACE VIEW pune_toppers AS
SELECT name, marks, city
FROM students
WHERE city = 'Pune' AND marks >= 75;

-- View काढणे
DROP VIEW pune_toppers;