Skip to content

Instantly share code, notes, and snippets.

@zkvarz

zkvarz/gist:abbd5a4943037985fd9e40fd71dd34c1

Created October 8, 2021 08:52
Show Gist options
  • Save zkvarz/abbd5a4943037985fd9e40fd71dd34c1 to your computer and use it in GitHub Desktop.
Save zkvarz/abbd5a4943037985fd9e40fd71dd34c1 to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL table relationships examples
Raw
gistfile1.txt
DROP schema IF EXISTS anime cascade; --USE ONLY IN PRE-PROD ENVIRONMENTS
DROP schema IF EXISTS audio cascade; --USE ONLY IN PRE-PROD ENVIRONMENTS
DROP schema IF EXISTS persons cascade; --USE ONLY IN PRE-PROD ENVIRONMENTS
DROP schema IF EXISTS students cascade; --USE ONLY IN PRE-PROD ENVIRONMENTS
create schema anime;
create table anime.anime
(
id serial not null,
name varchar not null
);
create unique index anime_id_uindex on anime.anime (id);
alter table anime.anime add constraint anime_pk primary key (id);
/* ******************************************
Just example with constraints
*********************************************/
create schema audio;
create table audio.audio_track
(
audio_track_id text not null,
audio_country_id text not null,
name text,
constraint audio_track_pkey
primary key (audio_track_id, audio_country_id)
);
create table audio.audio_chart
(
audio_chart_id text not null
constraint audio_chart_pkey
primary key,
audio_country_id text not null,
--audio_country_id text not null
--constraint audio_chart_country_id_fk
--references region,
name text not null
);
create table audio.audio_chart_track_statistic
(
chart_date date not null,
audio_track_id text not null,
audio_country_id text not null,
audio_chart_id text not null
constraint audio_chart_id_fk
references audio.audio_chart,
current_position smallint not null,
constraint audio_chart_track_statistic_pkey
primary key (audio_chart_id, current_position, chart_date),
constraint audio_track_id
foreign key (audio_track_id, audio_country_id) references audio.audio_track
);
-- for the reference:
-- https://github.com/RAstardzhiev/Software-University-SoftUni/tree/master/Databases%20Basics%20-%20MS%20SQL%20Server/Table-Relations-Exercises
-- https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de
-- https://www.sqlshack.com/learn-sql-types-of-relations/
create schema persons;
/* ******************************************
Problem 1. One-To-One Relationship
*********************************************/
CREATE TABLE persons.Passports
(
PassportID serial NOT NULL,
--PassportID INT IDENTITY(101, 1) NOT NULL,
PassportNumber CHAR(8) NOT NULL,
CONSTRAINT PK_Passports PRIMARY KEY(PassportID)
);
CREATE TABLE persons.Persons
(
PersonId serial NOT NULL, --PK
--PersonId INT IDENTITY NOT NULL,
FirstName VARCHAR(50) NOT NULL,
Salary DECIMAL(10, 2),
PassportID INT--UNIQUE
UNIQUE NOT NULL,
CONSTRAINT PK_Persons PRIMARY KEY(PersonId),
CONSTRAINT FK_Persons_Passports FOREIGN KEY(PassportID) REFERENCES persons.Passports(PassportID) ON DELETE CASCADE
);
INSERT INTO persons.Passports (PassportNumber)
VALUES
(
'N34FG21B'
),
(
'K65LO4R7'
),
(
'ZE657QP2'
);
INSERT INTO persons.Persons (FirstName, Salary, PassportID)
VALUES
(
'Roberto',
43300.00,
2
),
(
'Tom',
56100.00,
3
),
(
'Yana',
60200.00,
1
);
/* ******************************************
Problem 2. One-To-Many Relationship
*********************************************/
CREATE TABLE persons.Manufacturers
(
ManufacturerID serial NOT NULL,
Name VARCHAR(50) NOT NULL,
EstablishedOn DATE DEFAULT now(),
CONSTRAINT PK_Manufacturers PRIMARY KEY(ManufacturerID)
);
CREATE TABLE persons.Models
(
ModelID INT NOT NULL,
Name VARCHAR(50) NOT NULL,
ManufacturerID INT NOT NULL,
CONSTRAINT PK_Models PRIMARY KEY(ModelID),
CONSTRAINT FK_Models_Manufacturers FOREIGN KEY(ManufacturerID) REFERENCES persons.Manufacturers(ManufacturerID)
);
INSERT INTO persons.Manufacturers (Name, EstablishedOn)
VALUES
(
'BMW',
'07/03/1916'
),
(
'Tesla',
'01/01/2003'
),
(
'Lada',
'01/05/1966'
);
INSERT INTO persons.Models
VALUES
(
1,
'X1',
1
),
(
2,
'i6',
1
),
(
3,
'Model S',
2
),
(
4,
'Model X',
2
),
(
5,
'Model 3',
2
),
(
6,
'Nova',
3
);
/* ******************************************
One-To-Many Relationship QUERIES
--GET ALL Manufacturers and models
SELECT ma.manufacturerid, ma.name, ma.establishedon, modelid, mo.name, mo.manufacturerid
FROM persons.manufacturers ma, persons.models mo where ma.manufacturerid = mo.manufacturerid;
--OR:
SELECT * from persons.manufacturers manufacturers inner join persons.models models on manufacturers .manufacturerid = models.manufacturerid;
*********************************************/
/* ******************************************
Problem 3. Many-To-Many Relationship
*********************************************/
create schema students;
CREATE TABLE students.Students
(
StudentID serial NOT NULL,
Name VARCHAR(255) NOT NULL,
CONSTRAINT PK_Students PRIMARY KEY(StudentID)
);
CREATE TABLE students.Exams
(
ExamID INT NOT NULL,
Name VARCHAR(255) NOT NULL,
CONSTRAINT PK_Exams PRIMARY KEY(ExamID)
);
CREATE TABLE students.StudentsExams
(
StudentID INT NOT NULL,
ExamID INT NOT NULL,
CONSTRAINT PK_StudentsExams PRIMARY KEY(StudentID, ExamID),
CONSTRAINT FK_StudentsExams_Students FOREIGN KEY(StudentID) REFERENCES students.Students(StudentID),
CONSTRAINT FK_StudentsExams_Exams FOREIGN KEY(ExamID) REFERENCES students.Exams(ExamID)
);
INSERT INTO students.Students (name)
VALUES
(
'Mila'
),
(
'Toni'
),
(
'Ron'
);
INSERT INTO students.Exams
VALUES
(
1,
'SpringMVC'
),
(
2,
'Neo4j'
),
(
3,
'Oracle 11g'
);
INSERT INTO students.StudentsExams
VALUES
(
1,
1
),
(
1,
2
),
(
2,
1
),
(
3,
3
),
(
2,
2
),
(
2,
3
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment