Constraints, Defaults & Domains

9. Practice Problems

Each answer is runnable SQLite SQL. Try first, then expand.

1. Create a users table where email is unique and required, and name is required.
   এমন একটি users table বানান যেখানে email unique ও required এবং name required।
   ✨ Show Answer

   ans1.sql

   CREATE TABLE users (
       id    INTEGER PRIMARY KEY,
       name  TEXT NOT NULL,
       email TEXT NOT NULL UNIQUE
   );
   INSERT INTO users(name,email) VALUES('Arif','a@x.bd');
   SELECT * FROM users;

   Run Copy Open in SQLite Online

2. Add a CHECK so that price > 0 for all products.
   products.price > 0 — এই CHECK যোগ করুন।
   ✨ Show Answer

   ans2.sql

   CREATE TABLE products(
       id INTEGER PRIMARY KEY,
       name TEXT,
       price INTEGER CHECK(price > 0)
   );
   INSERT INTO products VALUES(1,'Walton TV',45000);
   SELECT * FROM products;

   Run Copy Open in SQLite Online

3. Define an orders table whose status can only be one of pending, paid, shipped, cancelled.
   orders.status শুধু এই চারটি মানের একটি হতে পারবে — এমন CHECK লিখুন।
   ✨ Show Answer

   ans3.sql

   CREATE TABLE orders(
       id INTEGER PRIMARY KEY,
       status TEXT NOT NULL CHECK(status IN ('pending','paid','shipped','cancelled'))
   );
   INSERT INTO orders VALUES(1,'pending');
   SELECT * FROM orders;

   Run Copy Open in SQLite Online

4. Create a posts table where created_at defaults to the current timestamp and view_count defaults to 0.
   created_at default = CURRENT_TIMESTAMP, view_count default = 0।
   ✨ Show Answer

   ans4.sql

   CREATE TABLE posts(
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       title TEXT,
       view_count INTEGER DEFAULT 0,
       created_at TEXT DEFAULT CURRENT_TIMESTAMP
   );
   INSERT INTO posts(title) VALUES('Hello');
   SELECT * FROM posts;

   Run Copy Open in SQLite Online

5. Set up an orders → customers foreign key with ON DELETE CASCADE. Then delete a customer and verify the orders are gone.
   customers delete হলে তার orders-ও মুছবে — CASCADE দিয়ে দেখান।
   ✨ Show Answer

   ans5.sql

   PRAGMA foreign_keys = ON;
   CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT);
   CREATE TABLE orders(
       id INTEGER PRIMARY KEY,
       cust_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
   );
   INSERT INTO customers VALUES(1,'Arif');
   INSERT INTO orders VALUES(10,1),(11,1);
   DELETE FROM customers WHERE id = 1;
   SELECT * FROM orders;

   Run Copy Open in SQLite Online

6. Same as above, but use ON DELETE SET NULL.
   এবার SET NULL দিয়ে করুন।
   ✨ Show Answer

   ans6.sql

   PRAGMA foreign_keys = ON;
   CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT);
   CREATE TABLE orders(
       id INTEGER PRIMARY KEY,
       cust_id INTEGER REFERENCES customers(id) ON DELETE SET NULL
   );
   INSERT INTO customers VALUES(1,'Arif');
   INSERT INTO orders VALUES(10,1);
   DELETE FROM customers WHERE id = 1;
   SELECT * FROM orders;

   Run Copy Open in SQLite Online

7. Define a library_books table where (title, author) pair is unique together (composite UNIQUE).
   title ও author একসাথে unique — composite UNIQUE লিখুন।
   ✨ Show Answer

   ans7.sql

   CREATE TABLE library_books(
       id INTEGER PRIMARY KEY,
       title TEXT NOT NULL,
       author TEXT NOT NULL,
       UNIQUE(title, author)
   );
   INSERT INTO library_books(title,author) VALUES('Padma Nadir Majhi','Manik B.');
   SELECT * FROM library_books;

   Run Copy Open in SQLite Online

8. Use a generated column to compute full_name from first and last.
   first ও last থেকে full_name = first||' '||last — generated column বানান।
   ✨ Show Answer

   ans8.sql

   CREATE TABLE people(
       id INTEGER PRIMARY KEY,
       first TEXT, last TEXT,
       full_name TEXT GENERATED ALWAYS AS (first || ' ' || last) VIRTUAL
   );
   INSERT INTO people(first,last) VALUES('Arif','Hossain');
   SELECT * FROM people;

   Run Copy Open in SQLite Online

9. In one paragraph, explain when to use CASCADE vs RESTRICT.
   CASCADE আর RESTRICT — কখন কোনটি? এক অনুচ্ছেদে।
   ✨ Show Answer

   Answer: Use CASCADE when the child rows have no meaning without their parent — like a blog post's tags or a cart's line items. Use RESTRICT (or NO ACTION) when the child has independent value and you want a human to decide first — like orders that belong to a customer (you almost never want a stray DELETE FROM customers to also delete a year of order history).

   CASCADE তখনই ব্যবহার করুন যখন parent ছাড়া child-এর কোনো অর্থ নেই (যেমন একটি post-এর tag-গুলো)। কিন্তু order, payment-এর মতো গুরুত্বপূর্ণ child-এর জন্য RESTRICT রাখাই নিরাপদ — যেন অসাবধানতাবশত পুরো history মুছে না যায়।

10. Why does SQLite require PRAGMA foreign_keys = ON?
    SQLite-এ এই PRAGMA কেন দরকার?
    ✨ Show Answer

    Answer: Foreign-key enforcement was added to SQLite later (3.6.19, 2009). To stay compatible with old applications, the SQLite team made it OFF by default. Every modern app should turn it on right after opening a connection: PRAGMA foreign_keys = ON;.

    SQLite-এ FK enforcement পরে যোগ হয়েছিল; পুরাতন app যেন না ভাঙে তাই default OFF। আধুনিক app-এ connection খুলেই PRAGMA foreign_keys = ON দিতে হবে।

11. Add a CHECK that ensures a Bangladeshi NID column has exactly 10, 13 or 17 digits.
    NID ১০, ১৩, বা ১৭ digit হবে — CHECK দিয়ে enforce করুন।
    ✨ Show Answer

    ans11.sql

    CREATE TABLE citizens(
        id INTEGER PRIMARY KEY,
        nid TEXT NOT NULL CHECK (length(nid) IN (10,13,17))
    );
    INSERT INTO citizens(nid) VALUES('1234567890');
    SELECT * FROM citizens;

    Run Copy Open in SQLite Online

12. Why is NULL different from "empty"? How does UNIQUE treat multiple NULL rows in SQLite?
    NULL আর "ফাঁকা" এক জিনিস নয় — UNIQUE কলামে একাধিক NULL কি সম্ভব?
    ✨ Show Answer

    Answer: NULL means "unknown / not applicable" — it is not equal to anything, not even another NULL. SQLite (and the SQL standard) allows multiple NULLs in a UNIQUE column, because each NULL is "unknown" and unknowns are not duplicates of each other. If you really want only one NULL, you need an extra constraint (or just NOT NULL).

    NULL মানে "অজানা" — দুটি NULL সমান নয়, কারণ অজানা কি অজানা সমান হতে পারে? তাই SQLite-এ UNIQUE কলামে একাধিক NULL row রাখা যায়। চাইলে NOT NULL যোগ করুন।

13. Demonstrate that an INSERT violating a CHECK actually fails — show the error path.
    CHECK ভঙ্গ হলে INSERT fail করে — দেখান।
    ✨ Show Answer

    ans13.sql

    CREATE TABLE marks(id INTEGER PRIMARY KEY, score INTEGER CHECK(score BETWEEN 0 AND 100));
    INSERT INTO marks(score) VALUES(85);
    -- The next line will fail with: CHECK constraint failed: marks
    INSERT INTO marks(score) VALUES(150);
    SELECT * FROM marks;

    Run Copy Open in SQLite Online

14. Define a transactions table with a STORED generated column net = amount - fee.
    net = amount - fee — STORED generated column বানান।
    ✨ Show Answer

    ans14.sql

    CREATE TABLE transactions(
        id INTEGER PRIMARY KEY,
        amount INTEGER NOT NULL,
        fee INTEGER NOT NULL DEFAULT 0,
        net INTEGER GENERATED ALWAYS AS (amount - fee) STORED
    );
    INSERT INTO transactions(amount,fee) VALUES(1000,15),(500,5);
    SELECT * FROM transactions;

    Run Copy Open in SQLite Online