الـ Constraints: PK و FK و UNIQUE و CHECK — الـ database بتحمي نفسها

DROP TABLE IF EXISTS enrollments, lessons, courses, users CASCADE;

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  name  text NOT NULL CHECK (length(name) BETWEEN 2 AND 50)
);
-- unique على الإيميل من غير ما يفرق الكبير والصغير
CREATE UNIQUE INDEX users_email_key ON users (lower(email));

CREATE TABLE courses (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug        text NOT NULL UNIQUE,
  price_cents integer NOT NULL DEFAULT 0 CONSTRAINT price_not_negative CHECK (price_cents >= 0),
  teacher_id  bigint REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE lessons (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  course_id bigint NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
  position  integer NOT NULL CHECK (position > 0),
  title     text NOT NULL,
  UNIQUE (course_id, position)
);

INSERT INTO users (email, name) VALUES ('[email protected]', 'مُنى'), ('[email protected]', 'عمر');
INSERT INTO courses (slug, price_cents, teacher_id) VALUES ('node', 19950, 1);
INSERT INTO lessons (course_id, position, title) VALUES (1, 1, 'intro'), (1, 2, 'modules');


-- كل سطر تحت بيخالف constraint
INSERT INTO users (email, name) VALUES ('[email protected]', 'مُنى التانية');
INSERT INTO users (email, name) VALUES ('[email protected]', 'a');
INSERT INTO users (email) VALUES ('[email protected]');
INSERT INTO courses (slug, price_cents) VALUES ('sql', -100);
INSERT INTO courses (slug) VALUES ('node');
INSERT INTO lessons (course_id, position, title) VALUES (99, 1, 'ghost');
INSERT INTO lessons (course_id, position, title) VALUES (1, 2, 'dup');

$ psql -f schema.sql
DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 1
INSERT 0 2
psql:schema.sql:32: ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (lower(email))=([email protected]) already exists.
psql:schema.sql:33: ERROR:  new row for relation "users" violates check constraint "users_name_check"
DETAIL:  Failing row contains (4, [email protected], a).
psql:schema.sql:34: ERROR:  null value in column "name" of relation "users" violates not-null constraint
DETAIL:  Failing row contains (5, [email protected], null).
psql:schema.sql:35: ERROR:  new row for relation "courses" violates check constraint "price_not_negative"
DETAIL:  Failing row contains (2, sql, -100, null).
psql:schema.sql:36: ERROR:  duplicate key value violates unique constraint "courses_slug_key"
DETAIL:  Key (slug)=(node) already exists.
psql:schema.sql:37: ERROR:  insert or update on table "lessons" violates foreign key constraint "lessons_course_id_fkey"
DETAIL:  Key (course_id)=(99) is not present in table "courses".
psql:schema.sql:38: ERROR:  duplicate key value violates unique constraint "lessons_course_id_position_key"
DETAIL:  Key (course_id, "position")=(1,
…

DROP TABLE IF EXISTS enrollments, lessons, courses, users CASCADE;

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  name  text NOT NULL CHECK (length(name) BETWEEN 2 AND 50)
);
-- unique على الإيميل من غير ما يفرق الكبير والصغير
CREATE UNIQUE INDEX users_email_key ON users (lower(email));

CREATE TABLE courses (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug        text NOT NULL UNIQUE,
  price_cents integer NOT NULL DEFAULT 0 CONSTRAINT price_not_negative CHECK (price_cents >= 0),
  teacher_id  bigint REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE lessons (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  course_id bigint NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
  position  integer NOT NULL CHECK (position > 0),
  title     text NOT NULL,
  UNIQUE (course_id, position)
);

INSERT INTO users (email, name) VALUES ('[email protected]', 'مُنى'), ('[email protected]', 'عمر');
INSERT INTO courses (slug, price_cents, teacher_id) VALUES ('node', 19950, 1);
INSERT INTO lessons (course_id, position, title) VALUES (1, 1, 'intro'), (1, 2, 'modules');


-- ON DELETE: إيه اللي يحصل للصفوف اللي بتشاور على حاجة اتمسحت؟
DELETE FROM users WHERE id = 1;  -- teacher_id ← NULL
SELECT slug, teacher_id FROM courses;

DELETE FROM courses WHERE slug = 'node';  -- الدروس تتمسح معاه (CASCADE)
SELECT count(*) AS lessons_left FROM lessons;

$ psql -f schema.sql
DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 1
INSERT 0 2
DELETE 1
 slug | teacher_id 
------+------------
 node |           
(1 row)

DELETE 1
 lessons_left 
--------------
            0
(1 row)

DROP TABLE IF EXISTS enrollments, lessons, courses, users CASCADE;

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  name  text NOT NULL CHECK (length(name) BETWEEN 2 AND 50)
);
-- unique على الإيميل من غير ما يفرق الكبير والصغير
CREATE UNIQUE INDEX users_email_key ON users (lower(email));

CREATE TABLE courses (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug        text NOT NULL UNIQUE,
  price_cents integer NOT NULL DEFAULT 0 CONSTRAINT price_not_negative CHECK (price_cents >= 0),
  teacher_id  bigint REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE lessons (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  course_id bigint NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
  position  integer NOT NULL CHECK (position > 0),
  title     text NOT NULL,
  UNIQUE (course_id, position)
);

INSERT INTO users (email, name) VALUES ('[email protected]', 'مُنى'), ('[email protected]', 'عمر');
INSERT INTO courses (slug, price_cents, teacher_id) VALUES ('node', 19950, 1);
INSERT INTO lessons (course_id, position, title) VALUES (1, 1, 'intro'), (1, 2, 'modules');


CREATE TABLE enrollments (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id   bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  course_id bigint NOT NULL REFERENCES courses (id) ON DELETE RESTRICT,
  progress  integer NOT NULL DEFAULT 0
    CONSTRAINT enrollments_progress_range CHECK (progress BETWEEN 0 AND 100),
  status    text NOT NULL DEFAULT 'active'
    CONSTRAINT enrollments_status_valid CHECK (status IN ('active', 'completed', 'refunded')),
  CONSTRAINT enrollments_user_course_key UNIQUE (user_id, course_id)
);

INSERT INTO enrollments (user_id, course_id) VALUES (2, 1);
INSERT INTO enrollments (user_id, course_id) VALUES (2, 1);
INSERT INTO enrollments (user_id, course_id, progress) VALUES (1, 1, 150);
INSERT INTO enrollments (user_id, course_id, status) VALUES (1, 1, 'paused');
DELETE FROM courses WHERE id = 1;

SELECT user_id, course_id, progress, status FROM enrollments;

$ psql -f schema.sql
DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 1
INSERT 0 2
CREATE TABLE
INSERT 0 1
psql:schema.sql:43: ERROR:  duplicate key value violates unique constraint "enrollments_user_course_key"
DETAIL:  Key (user_id, course_id)=(2, 1) already exists.
psql:schema.sql:44: ERROR:  new row for relation "enrollments" violates check constraint "enrollments_progress_range"
DETAIL:  Failing row contains (3, 1, 1, 150, active).
psql:schema.sql:45: ERROR:  new row for relation "enrollments" violates check constraint "enrollments_status_valid"
DETAIL:  Failing row contains (4, 1, 1, 0, paused).
psql:schema.sql:46: ERROR:  update or delete on table "courses" violates foreign key constraint "enrollments_course_id_fkey" on table "enrollments"
DETAIL:  Key (id)=(1) is still referenced from table "enrollments".
 user_id | course_id | progress | status 
---------+-----------+----------+--------
       2 |         1 |        0 | active
(1 row)