This design will be for a photo-sharing app.
There will be four tables:
So what tables should we make?
When you establish the link between different records, you can then begin to implement the relationship.
In our particular case, we would say the following:
This won't be used in our example, but it is important to know about these early in the piece.
Examples of one-to-one:
Examples of many-to-many:
Whenever we talk about relationships, we are always going to need a foreign key.
In our example with one-to-many relationships, we will decide that the foreign key is the id of the "one" part of that relationship.
From our example:
user_id
and photo_id
.user_id
and photo_id
.user_id
.Notes on Primary Keys:
Notes on Foreign Keys:
xyz_id
.The SERIAL
keyword will autogenerated auto-incrementing numbers.
CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL );
We can then add some users to see what happens:
INSERT INTO users (username) VALUES ('Bob'), ('Billy'), ('Bilfred'), ('Bilbo'); SELECT * FROM users;
We get back the following table:
id | username |
---|---|
1 | Bob |
2 | Billy |
3 | Bilfred |
4 | Bilbo |
We can use REFERENCES
to create our foreign key.
CREATE TABLE photos ( id SERIAL PRIMARY KEY, url VARCHAR(200) NOT NULL, user_id INTEGER REFERENCES users(id) );
We can the insert some data:
INSERT INTO photos (url, user_id) VALUES ('https://one.jpg', 4); SELECT * FROM photos;
We will get back our photo with the user_id
reference.
Before we go too far, we are going to insert some more photos.
INSERT INTO photos (url, user_id) VALUES ('https://1.jpg', 1), ('https://2.jpg', 1), ('https://3.jpg', 1), ('https://4.jpg', 2), ('https://5.jpg', 3), ('https://6.jpg', 4);
Now that we have data from here, let's find a way to retrieve these photos in a meaningful way like "find photos created by user X".
SELECT * FROM photos WHERE user_id = 4;
In our example, this will bring back two photos with user id 4.
The next example is us using a join statement.
SELECT url, username FROM photos JOIN users ON users.id = photos.user_id;
From that, we get a list back of the urls and usernames of the photos when joined by the user record.
For example, if we create a photo, we want to make sure that the user exists.
If we insert a photo that refers to a user that doesn't exist, we will get an error from a foreign key constraint.
If we insert a photo with no user, it will be successful (unless we enforce the scenario that it must exist).
What happens if we delete a user that has photos? If we do not cascade delete, then we will have photos with dangling foreign keys.
We have some options for the DELETE option that we can use:
Delete option | What happen if we delete a user that has photos? |
---|---|
ON DELETE RESTRICT (default) | Throw an error |
ON DELETE NO ACTION | Throw an error |
ON DELETE CASCADE | Delete the photo too |
ON DELETE SET NULL | Set the user_id of the photo to NULL |
ON DELETE SET DEFAULT | Set the user_id of the photo to the default value (if one is provided) |
Note: There is a difference between RESTRICT and NO ACTION that will be explored later.
To enforce these other delete options, we must create the table with that constraint:
CREATE TABLE photos ( id SERIAL PRIMARY KEY, url VARCHAR(200) NOT NULL, user_id INTEGER REFERENCES users(id) ON DELETE CASCADE );