Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE school_students (
    id BIGSERIAL PRIMARY KEY,
    school_id character varying NOT NULL,
    student_id character varying NOT NULL
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX school_students_school_student_idx ON school_students(school_id text_ops, student_id text_ops);

CREATE TABLE id_cards (
    id BIGSERIAL PRIMARY KEY,
    school_student_id bigint NOT NULL REFERENCES school_students(id)
);

-- Indices -------------------------------------------------------

CREATE INDEX id_cards_school_student_id_idx ON id_cards(school_student_id int8_ops);

-- sample school_student data
INSERT INTO school_students (school_id, student_id) 
SELECT 1, g.id
FROM generate_series(0, 200) AS g (id);

INSERT INTO school_students (school_id, student_id) 
SELECT 2, g.id
FROM generate_series(0, 10000) AS g (id);

INSERT INTO school_students (school_id, student_id) 
SELECT 3, g.id
FROM generate_series(0, 20000) AS g (id);

-- sample id_card data
INSERT INTO id_cards (school_student_id) 
SELECT g.id
FROM generate_series(1, 200) AS g (id);

INSERT INTO id_cards (school_student_id) 
SELECT g.id
FROM generate_series(201, 10200) AS g (id);

INSERT INTO id_cards (school_student_id) 
SELECT g.id
FROM generate_series(10101, 30200) AS g (id);

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE INDEX

INSERT 0 201

INSERT 0 10001

INSERT 0 20001

INSERT 0 200

INSERT 0 10000

INSERT 0 10000

INSERT 0 20100