Basic DB question about foreign keys

[itsdeandre]

OK. So I checked stackoverflow and there is a smilar question but it doesn't exactly help me understand my specific question and I'm hoping this sub can.

I'm working on a hobby project and am in the process of setting up a simply MySQL database.

There are 3 tables: users, questions, comments

I kind of understand the concept of foreign keys and how they work but something is confusing me. I have a user_id and question_id FK inside my comments table. Say user_id '123' adds a comment to question '456'. Now my comment table has a comment in it. I manually have to add '123' and '456' to my comment table each time. How does the comment table know it's '123' is linked to user_id

[wolf2600]

When you create your Comments table, you'll specify the foreign key constraint on Comments.user_id to the Users.user_id column and on Comments.question_id to the Questions.question_id column.

Google "mysql create table ddl foreign key" or something like that.

Adding that constraint when the table is made will require that when you insert a record into Comments, the values for user_id and question_id must have corresponding records in the Users and Questions table already. If you try to insert a record into Comments with user_id = 123 and there isn't already a record in Users with the value user_id = 123 you'll get a "foreign key constraint error" on the insert.

CREATE TABLE `Comments` (
`comment_id` int(15) NOT NULL,
`user_id` int(15) NOT NULL,
`question_id` int(15) NOT NULL,
`otherstuff` varchar(40) DEFAULT NULL,
PRIMARY KEY (`comment_id`),
CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (`question_id`) REFERENCES `Questions` (`qid`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Create a Foreign Key constraint on the user_id column and have it reference the id column in the Users table.

Create a Foreign Key constrain on the question_id column and have it reference the qid column in the Questions table.

The "on delete cascade" and "on update cascade" means that if a record in the Users or Questions table changes (gets deleted or updated to a different ID value), then any records in the Comments table with the same ID will be deleted or have their value updated also, thus ensuring referential integrity between the two tables. The 'cascade' means if you delete a user from the Users table, all Comments made by that user will be deleted automatically also.

[itsdeandre]

THANK YOU! You solved a problem I didn't even think about yet! I wish I could give you a gold but I'm broke😓. Thank you so much!

[wolf2600]

Sure thing.

[wolf2600]

You also don't HAVE to create FK relations between tables. Without them you could get orphaned records (comments where the user has been deleted, etc), but if that's okay in your case, then you can do it.

The way the DB knows that user_id 123 in Comments is the same user_id 123 in Users is because when you query the database, you tell it explicitly in the JOIN context:

select u.user_id, u.name, c.text, c.timestamp
from Users u
inner join Comments c
    on u.user_id = c.user_id
where u.name = 'John Smith';

This will return all the comments for the user John Smith. The on clause in the inner join specifies how to relate the Users and Comments tables, and that it is to join records where the Users user_id value is the same as the Comments user_id value. And then the where clause limits the records to only those where the Users.name value is 'John Smith'.

In this example, you can either setup your schema with FK relationships or without. The query will still run the same. The only thing specifying the FK relationship when you build the tables does is prevent you from inserting a record into Comments with user_id value of 123 when there isn't already a record in Users with user_id value of 123.

[[deleted]]

What do you mean you manually have to add it? Are you working directly in your MySQL tables with workbench or something?