SQL Course: One-to-one Relationships and Inner Joins.

In this chapter, we will set up a user and a profile table and create a one-to-one relationship between them.

A user can create a user with a username and password and then create a profile with a profile image and bio. A user can only have one profile and a profile can only be related to one user. That is why it is called a one-to-one relationship.

If you use Replit, create the template for SQLite and you are good to go.

Prerequisites with links

Press the links to read more about the topic;

• Create a table
• Primary Keys
• Foreign Keys
• Autoincrement
• Data types
• Inner join
• Contraints
• Aliases

Create the tables.

Leave out the primary and foreign keys in the profile table for now. We use the constraint CHECK to make sure the password has a length of more than 5 characters.

CREATE TABLE Users 
(
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  Username VARCHAR(255) UNIQUE NOT NULL,
  Password VARCHAR(255) NOT NULL,
  CHECK (LENGTH(Password) > 5)
);

CREATE TABLE Profiles 
(
  Img VARCHAR(1),
  Bio TEXT
);

Establish the one-to-one relationship.

To make sure a user only can create one profile, we can use the user’s primary key as the profile’s primary and foreign key. This way the profile is dependent on the user.

CREATE TABLE Profiles
(
  UserID INTEGER NOT NULL PRIMARY KEY,
  Img VARCHAR(1),
  Bio TEXT,
  FOREIGN KEY (UserID) REFERENCES Users(ID)
);

Insert data to the user and profile.

INSERT INTO Users (Username, Password) VALUES 
  ('Ben', 'secret'),
  ('Jim', 'secret'),
  ('Luk', 'secret');

INSERT INTO Profiles (UserID, Img, BIO) VALUES
  (1, '😎', 'I am a cool guy'),
  (2, '🥳', 'I love party'),
  (3, '🤠', 'I am a cowboy');

Query user and profile information using inner join.

• We use Aliases to give the temporary names u for Users and p for Profiles.
• Use SELECT to choose what fields you want selected from the database.
• Join the Profiles with the Users where the ID's match.

SELECT u.Username, p.Img, p.Bio
FROM Profiles p
JOIN Users u ON p.UserID = u.ID;

Result:
Ben|😎|I am a cool guy
Jim|🥳|I love party
Luk|🤠|I am a cowboy