Join the Conversation!
Subscribing gives you access to the comments so you can share your ideas, ask questions, and connect with others.
Connecting Tables - Solution
Previously you learned how to insert Users with the statement.
INSERT INTO Users (name, email, age) VALUES
("Oscar","oscar@email.com", 25),
("Mario","mario@email.com", 30),
("Andrea","andrea@email.com", 40);You inserted into:
- name
- age
In order to the table, we'll do the same. But we'll need to make sure we include the in the fields we want to .
INSERT INTO Posts (title,content,userId) VALUES
("My First Post","My Name is Oscar.", 1),
("Mario's New Post","Mario's blog is online!", 2),
("Mario's Second Post","Hi mom!", 2),
("Why Andrea is the Best","Some Post content Here", 3);Here we've inserted into the following columns:
- title
- content
- userId
Because , , and have already been inserted into the Database, and because we know the for each User is , we know has an of and has an
of and so on.
For example the following code linked the "My First Post" to the user who's id is :
("My First Post","My Name is Oscar.", 1)So to link them to their posts, we just add the correct number referencing their to the field.
Your whole Database setup should look like this:
CREATE TABLE Users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
age INT NOT NULL,
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE Posts (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
content TEXT NOT NULL,
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
# add the userId to the table
userId INT NOT NULL,
# Assign the userId as the FOREIGN KEY
FOREIGN KEY (userId) REFERENCES Users(id)
);
INSERT INTO Users (name, email, age) VALUES
("Oscar","oscar@email.com", 25),
("Mario","mario@email.com", 30),
("Andrea","andrea@email.com", 40);
INSERT INTO Posts (title,content,userId) VALUES
("My First Post","My Name is Oscar.",1),
("Mario's New Post","Mario's blog is online!",2),
("Mario's Second Post","Hi mom!",2),
("Why Andrea is the Best","Some Post content Here",3);And you should be ready to begin querying the database for posts based on users!
0 Comments
"Please login to view comments"
Upgrade your account