Now that we have two tables- we can begin to link them together. Let's imagine we have our Users and Posts table already filled with some data.

We have two posts and two users. But who made the posts? Ideally we'd have some between posts and users. The type of relation we're going to build here is called a relation.

One can have Many . This is the simplest type of relation we can create between tables- and all it requires is a 'reference' on the table that will have "many" references to it. In this case, posts will need a "user" reference.

Let's imagine that Amir made both of those posts.

We'll add a reference to Amir's userID to both of the posts:

The column of the Post table the of the User table. This gives us a clear way to connect posts to users. All posts will have an extra ID to let us know which user made each post.

SQL

In order to add this reference to our table we'll need to add two things.

• The column "userId"
• A 'FOREIGN KEY' contraint telling SQL to link this column to the table

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)
);

Now our Posts table can contain references to our Users table. Go ahead and add this Posts table to our Database.

You've already learned how to data into a table, so now give it a shot here. Add some users to our table, and add some posts to our table with references to those Users.

0 Comments

Top comments

"Please login to view comments"

Join the Conversation!

Subscribing gives you access to the comments so you can share your ideas, ask questions, and connect with others.

Upgrade your account