CREATE, ALTER, DROP আর কনস্ট্রেইন্ট

CREATE TABLE IF NOT EXISTS reviews (
    review_id   INTEGER PRIMARY KEY AUTOINCREMENT,                -- ইউনিক id, নিজে থেকেই বসে
    product_id  INTEGER NOT NULL,                                 -- আসল কোনো প্রোডাক্ট হতে হবে (নিচের FOREIGN KEY)
    customer_id INTEGER,                                          -- NULL = নামহীন রিভিউ
    rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    body        TEXT NOT NULL,
    label       TEXT CONSTRAINT valid_label
                     CHECK (label IN ('positive', 'neutral', 'negative')),
    status      TEXT NOT NULL DEFAULT 'new',
    UNIQUE (product_id, customer_id),                             -- এক কাস্টমার এক প্রোডাক্টে একটাই রিভিউ
    FOREIGN KEY (product_id) REFERENCES products (product_id),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

INSERT INTO reviews (product_id, customer_id, rating, body, label)
VALUES (3, 2, 5, 'Keys feel great, a bit loud.', 'positive'),
       (4, 7, 2, 'Stopped working after a week.', 'negative');

SELECT * FROM reviews;

+-----------+------------+-------------+--------+-------------------------------+----------+--------+
| review_id | product_id | customer_id | rating | body                          | label    | status |
+-----------+------------+-------------+--------+-------------------------------+----------+--------+
|         1 |          3 |           2 |      5 | Keys feel great, a bit loud.  | positive | new    |
|         2 |          4 |           7 |      2 | Stopped working after a week. | negative | new    |
+-----------+------------+-------------+--------+-------------------------------+----------+--------+

INSERT INTO reviews (product_id, customer_id, rating) VALUES (4, 2, 4);

Error: NOT NULL constraint failed: reviews.body

INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (4, 2, 9, 'Amazing!');

Error: CHECK constraint failed: rating BETWEEN 1 AND 5

INSERT INTO reviews (product_id, customer_id, rating, body, label)
VALUES (4, 2, 4, 'Good value.', 'postive');

Error: CHECK constraint failed: valid_label

INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (3, 2, 4, 'Changed my mind.');

Error: UNIQUE constraint failed: reviews.product_id, reviews.customer_id

INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (99, 2, 4, 'Which product?');

Error: FOREIGN KEY constraint failed

INSERT INTO reviews (review_id, product_id, customer_id, rating, body) VALUES (1, 5, 2, 5, 'Sharp.');

Error: UNIQUE constraint failed: reviews.review_id

INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (5, NULL, 4, 'Great colours.');
INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (5, NULL, 3, 'Too big for my desk.');

SELECT review_id, product_id, customer_id, rating, label FROM reviews ORDER BY review_id;

+-----------+------------+-------------+--------+----------+
| review_id | product_id | customer_id | rating | label    |
+-----------+------------+-------------+--------+----------+
|         1 |          3 |           2 |      5 | positive |
|         2 |          4 |           7 |      2 | negative |
|         3 |          5 | NULL        |      4 | NULL     |
|         4 |          5 | NULL        |      3 | NULL     |
+-----------+------------+-------------+--------+----------+

import sqlite3

raw = sqlite3.connect("shop.db")                 # সাধারণ কানেকশন: কোনো PRAGMA নেই
print("foreign_keys =", raw.execute("PRAGMA foreign_keys").fetchone()[0])

raw.execute("INSERT INTO reviews (product_id, rating, body) VALUES (999, 5, 'Orphan review')")
print("orphans:", raw.execute("SELECT COUNT(*) FROM reviews WHERE product_id = 999").fetchone()[0])

raw.rollback()                                   # বাতিল করে তারপর বন্ধ করুন
raw.close()

foreign_keys = 0
orphans: 1

-- MySQL 8.0: ইনলাইন REFERENCES মেনে নেয়, তারপর উপেক্ষা করে
CREATE TABLE wishlist (
    wish_id    INT PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products (product_id)   -- কোনো ফরেন কি তৈরি হয় না
);
INSERT INTO wishlist (wish_id, product_id) VALUES (1, 999);

SELECT * FROM wishlist;

+---------+------------+
| wish_id | product_id |
+---------+------------+
|       1 |        999 |
+---------+------------+

-- MySQL
CREATE TABLE reviews (
    review_id   INT AUTO_INCREMENT PRIMARY KEY,
    product_id  INT NOT NULL,
    customer_id INT,
    rating      INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    body        TEXT NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'new',
    UNIQUE (product_id, customer_id),
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);

INSERT INTO reviews (product_id, customer_id, rating, body)
VALUES (3, 2, 5, 'Great keys'), (4, 7, 2, 'Broke fast');

SELECT review_id, product_id, rating, status FROM reviews ORDER BY review_id;
INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (4, 2, 9, 'Amazing!');

+-----------+------------+--------+--------+
| review_id | product_id | rating | status |
+-----------+------------+--------+--------+
|         1 |          3 |      5 | new    |
|         2 |          4 |      2 | new    |
+-----------+------------+--------+--------+
Error: ERROR 3819: Check constraint 'reviews_chk_1' is violated.

-- PostgreSQL
CREATE TABLE reviews (
    review_id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id  INTEGER NOT NULL,
    customer_id INTEGER,
    rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    body        TEXT NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'new',
    UNIQUE (product_id, customer_id),
    FOREIGN KEY (product_id) REFERENCES products (product_id),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

INSERT INTO reviews (product_id, customer_id, rating, body)
VALUES (3, 2, 5, 'Great keys'), (4, 7, 2, 'Broke fast');

SELECT review_id, product_id, rating, status FROM reviews ORDER BY review_id;
INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (4, 2, 9, 'Amazing!');

+-----------+------------+--------+--------+
| review_id | product_id | rating | status |
+-----------+------------+--------+--------+
|         1 |          3 |      5 | new    |
|         2 |          4 |      2 | new    |
+-----------+------------+--------+--------+
Error: new row for relation "reviews" violates check constraint "reviews_rating_check"

-- PostgreSQL
INSERT INTO reviews (review_id, product_id, customer_id, rating, body) VALUES (7, 5, 2, 4, 'Sharp.');

Error: cannot insert a non-DEFAULT value into column "review_id"

ALTER TABLE reviews ADD COLUMN helpful_votes INTEGER NOT NULL DEFAULT 0;
ALTER TABLE reviews RENAME COLUMN body TO review_text;     -- SQLite 3.25+
ALTER TABLE reviews DROP COLUMN status;                    -- SQLite 3.35+

SELECT review_id, rating, review_text, helpful_votes FROM reviews ORDER BY review_id;

+-----------+--------+-------------------------------+---------------+
| review_id | rating | review_text                   | helpful_votes |
+-----------+--------+-------------------------------+---------------+
|         1 |      5 | Keys feel great, a bit loud.  |             0 |
|         2 |      2 | Stopped working after a week. |             0 |
|         3 |      4 | Great colours.                |             0 |
|         4 |      3 | Too big for my desk.          |             0 |
+-----------+--------+-------------------------------+---------------+

ALTER TABLE reviews ADD COLUMN verified INTEGER NOT NULL;

Error: Cannot add a NOT NULL column with default value NULL

ALTER TABLE reviews ALTER COLUMN review_text TYPE VARCHAR(500);

Error: near "ALTER": syntax error

-- PostgreSQL
ALTER TABLE reviews ALTER COLUMN body TYPE VARCHAR(500);
ALTER TABLE reviews ADD COLUMN helpful_votes INTEGER NOT NULL DEFAULT 0;
ALTER TABLE reviews ADD CONSTRAINT votes_not_negative CHECK (helpful_votes >= 0);
UPDATE reviews SET helpful_votes = -1 WHERE review_id = 1;

Error: new row for relation "reviews" violates check constraint "votes_not_negative"

-- MySQL
ALTER TABLE reviews MODIFY COLUMN body VARCHAR(500) NOT NULL;   -- MySQL-এ পুরো কলামটা আবার লিখতে হয়
ALTER TABLE reviews RENAME COLUMN status TO review_status;
ALTER TABLE reviews ADD COLUMN helpful_votes INT NOT NULL DEFAULT 0;

SELECT review_id, body, review_status, helpful_votes FROM reviews ORDER BY review_id;

+-----------+------------+---------------+---------------+
| review_id | body       | review_status | helpful_votes |
+-----------+------------+---------------+---------------+
|         1 | Great keys | new           |             0 |
|         2 | Broke fast | new           |             0 |
+-----------+------------+---------------+---------------+

DROP TABLE customers;

Error: FOREIGN KEY constraint failed

-- PostgreSQL
TRUNCATE TABLE customers;

Error: cannot truncate a table referenced in a foreign key constraint

CREATE TABLE products_copy AS SELECT * FROM products;

SELECT name, type, "notnull", pk FROM pragma_table_info('products') ORDER BY cid;
SELECT name, type, "notnull", pk FROM pragma_table_info('products_copy') ORDER BY cid;

+-------------+--------------+---------+----+
| name        | type         | notnull | pk |
+-------------+--------------+---------+----+
| product_id  | INTEGER      |       0 |  1 |
| name        | VARCHAR(100) |       1 |  0 |
| category_id | INTEGER      |       0 |  0 |
| price       | INTEGER      |       1 |  0 |
| stock       | INTEGER      |       0 |  0 |
+-------------+--------------+---------+----+
+-------------+------+---------+----+
| name        | type | notnull | pk |
+-------------+------+---------+----+
| product_id  | INT  |       0 |  0 |
| name        | TEXT |       0 |  0 |
| category_id | INT  |       0 |  0 |
| price       | INT  |       0 |  0 |
| stock       | INT  |       0 |  0 |
+-------------+------+---------+----+

DELETE FROM products_copy;                 -- SQLite-এর "truncate"
SELECT COUNT(*) AS rows_left FROM products_copy;
DROP TABLE products_copy;
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS reviews;              -- দ্বিতীয়বার: IF EXISTS থাকায় কিছুই হয় না

+-----------+
| rows_left |
+-----------+
|         0 |
+-----------+

-- সহজ
CREATE TABLE newsletter (
    subscriber_id INTEGER PRIMARY KEY,
    email         TEXT NOT NULL UNIQUE,
    language      TEXT NOT NULL DEFAULT 'bn'
);
INSERT INTO newsletter (email) VALUES ('rafiq@example.com');
SELECT * FROM newsletter;

+---------------+-------------------+----------+
| subscriber_id | email             | language |
+---------------+-------------------+----------+
|             1 | rafiq@example.com | bn       |
+---------------+-------------------+----------+

-- মাঝারি
CREATE TABLE model_runs (
    run_id     INTEGER PRIMARY KEY,
    model_name TEXT NOT NULL,
    accuracy   REAL NOT NULL CONSTRAINT accuracy_0_to_1 CHECK (accuracy BETWEEN 0 AND 1),
    status     TEXT NOT NULL DEFAULT 'running'
               CHECK (status IN ('running', 'finished', 'failed'))
);
INSERT INTO model_runs (model_name, accuracy) VALUES ('logreg-v1', 0.87);
INSERT INTO model_runs (model_name, accuracy) VALUES ('logreg-v2', 1.7);

Error: CHECK constraint failed: accuracy_0_to_1

-- কঠিন: SQLite-এ টেবিল নতুন করে বানানোর রেসিপি
BEGIN;
CREATE TABLE newsletter_new (
    subscriber_id INTEGER PRIMARY KEY,
    email         TEXT NOT NULL UNIQUE,
    language      TEXT NOT NULL DEFAULT 'bn' CHECK (language IN ('bn', 'en'))
);
INSERT INTO newsletter_new (subscriber_id, email, language)
SELECT subscriber_id, email, language FROM newsletter;
DROP TABLE newsletter;
ALTER TABLE newsletter_new RENAME TO newsletter;
COMMIT;

SELECT * FROM newsletter;
INSERT INTO newsletter (email, language) VALUES ('mitu@example.com', 'fr');

+---------------+-------------------+----------+
| subscriber_id | email             | language |
+---------------+-------------------+----------+
|             1 | rafiq@example.com | bn       |
+---------------+-------------------+----------+
Error: CHECK constraint failed: language IN ('bn', 'en')