Relations

One to one

create table users (
	id bigint primary key generated always as identity,
	username text not null
);

create table user_profile_one_to_one (
	user_id bigint primary key,
	bio text,
	age int,
	
	constraint user_id_fk foreign key (user_id) references users(id) on delete cascade
);
-- one to one achieved by having user_id as a primary and foreign key

One to many

create table users (
	id bigint primary key generated always as identity,
	username text not null
);
	
create table user_profile (
	id bigint primary key generated always as identity,
	user_id bigint,
	bio text,
	age int,
	
	constraint user_id_fk foreign key (user_id) references users(id) on delete cascade
);

Many to many

create table students (
    id bigint primary key generated always as identity,
    name text
);

create table courses (
    id bigint primary key generated always as identity,
    title text
);

create table enrollments (
    student_id bigint,
    course_id bigint,
    primary key (student_id, course_id),
    foreign key (student_id) references students(id) on delete cascade,
    foreign key (course_id) references courses(id) on delete cascade
);