PostgreSQL - Day 8

Mon, Jun 13, 2022
4-minute read

Keep learning PostgreSQL by Learn PostgreSQL Tutorial - Full Course for Beginners.

Today we’ll learn the Relationship, Foreign Keys, and how to JOIN tables.

45. What Is A Relationship/Foreign Keys

You could have multiple tables and then connect them based on a foreign key.

A foreign key is a column that references a primary key in another table. For example, a car can be owned by a person.

It’s a constraint preventing the relation between tables from being broken by other actions.

FK and PK should be the same type.

46. Adding Relationships Between Tables

Case:

One person can only have one car.
One car can only belong to one person.

Current DB:

create table person (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	email VARCHAR(150),
	gender VARCHAR(50) NOT NULL,
	date_of_birth DATE NOT NULL,
	country_of_birth VARCHAR(50)
);

create table car (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	make VARCHAR(100) NOT NULL,
	model VARCHAR(100) NOT NULL,
	price NUMERIC(19, 2) NOT NULL
);

To create a relationship between these tables, drop these tables first.

And add the below statements at the end of the statement, which creates a person table.

car_id BIGINT REFERENCES car(id),
UNIQUE(car_id)

UNIQUE makes a car can only belong to a person.

The current statements would be

create table person (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	email VARCHAR(150),
	gender VARCHAR(50) NOT NULL,
	date_of_birth DATE NOT NULL,
	country_of_birth VARCHAR(50),
    car_id BIGINT REFERENCES car(id),
    UNIQUE(car_id)
);

create table car (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	make VARCHAR(100) NOT NULL,
	model VARCHAR(100) NOT NULL,
	price NUMERIC(19, 2) NOT NULL
);

If you paste the statements directly, you will get an error because the car table doesn’t exist.

To solve it, place the statement which creates car table at the first.

create table car (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	make VARCHAR(100) NOT NULL,
	model VARCHAR(100) NOT NULL,
	price NUMERIC(19, 2) NOT NULL
);

create table person (
	id BIGSERIAL NOT NULL PRIMARY KEY,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	email VARCHAR(150),
	gender VARCHAR(50) NOT NULL,
	date_of_birth DATE NOT NULL,
	country_of_birth VARCHAR(50),
    car_id BIGINT REFERENCES car(id),
    UNIQUE(car_id)
);

insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Mic', 'Cormode', null, 'Bigender', '2021-09-16', 'Sweden');
insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Tristam', 'Chestnut', 'tchestnut1@stanford.edu', 'Male', '2021-12-29', 'Poland');
insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Fidole', 'Dallison', 'fdallison2@amazon.com', 'Male', '2022-03-22', 'Sweden');

insert into car (id, make, model, price) values (1, 'BMW', 'Alpina B7', '85907.51');
insert into car (id, make, model, price) values (2, 'Dodge', 'Dakota', '67916.80');

Use \i to execute it.

Result

47. Updating Foreign Keys Columns

The current tables

Add car which id = 2 to person whose id = 1

UPDATE person SET car_id = 2 WHERE id = 1;

If you try to add a car ID that doesn’t exist, you will get an error.
- As mentioned previously, Foreign Keys prevent the relationship between the tables from being broken by actions.
- You can only assign a foreign key when there is a relation in the other table.

48. Inner Joins

The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.

Syntax

SELECT * FROM table1
JOIN table2
ON table1.col_name = table2.col_name;

example

SELECT * FROM person
JOIN car 
ON person.car_id = car.id;

Tips: use \x to have an expanded display. You can turn it off with the same command.

You can also use table.col to select data partially.

49. Left Joins

The LEFT JOIN returns all rows from the left table and the matching rows from the right table.

Syntax

SELECT * FROM table1
LEFT JOIN table2
ON table1.col_name = table2.col_name;

Example: query people either have a car or not.

SELECT * FROM person
LEFT JOIN car 
ON person.car_id = car.id;

Advanced example: left join and select people who don’t have a car

SELECT * FROM person
LEFT JOIN car 
ON person.car_id = car.id
WHERE car.* IS NULL;

learn PostgreSQL Database