CREATE TABLE posts (
postId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
author VARCHAR(24) NOT NULL,
description TEXT NOT NULL,
createdAt TIMESTAMP,
PRIMARY KEY (postId)
);
CREATE TABLE comments(
commentId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
comment TEXT NOT NULL,
postId INT(11),
userId INT(11),
createdAt TIMESTAMP,
PRIMARY KEY (commentId),
FOREIGN KEY (userId) REFERENCES users(userId),
FOREIGN KEY (postId) REFERENCES posts(postId)
);
CREATE TABLE replies (
repId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
reply TEXT NOT NULL,
userId INT(11),
commentId INT(11),
createdAt TIMESTAMP,
PRIMARY KEY (repId),
FOREIGN KEY (userId) REFERENCES users(userId),
FOREIGN KEY (commentId) REFERENCES comments(commentId)
);
CREATE TABLE users (
userId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
userName VARCHAR(100) NOT NULL,,
email VARCHAR(100) NOT NULL,
PRIMARY KEY (userId)
);
how to retrive userName,comment, and createdAt from users and comments table while I have used userId as a Foreign key on the comment table
if it isn't correct, correct me please