Foreign keys are a data integrity check, nothing more. It ensures that the fields in the child table contain values that appear in the referenced fields in the parent table. That's it^*.

You cannot use foreign keys to merge data from one table into another table. However, theJOINstatement does exactly what you need, and used with the foreign key ensures that each post has a valid username to identify the correct user data.

Take this example:

User data

CREATE TABLE `userdata` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(45) NOT NULL, `displayName` varchar(45) NOT NULL, `email` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id_UNIQUE` (`id`), UNIQUE KEY `username_UNIQUE` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

Post

CREATE TABLE `posts` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(45) NOT NULL, `message` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id_UNIQUE` (`id`), KEY `fk_posts_userdata_idx` (`username`), CONSTRAINT `fk_posts_userdata` FOREIGN KEY (`username`) REFERENCES `userdata` (`username`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

We canJOINtheuserdatatable with thepoststable using the following query:

select `posts`.`id` AS `postId`, `posts`.`username` AS `username`, `posts`.`message` AS `message`, `userdata`.`id` AS `userId`, `userdata`.`displayName` AS `displayName`, `userdata`.`email` AS `email` from (`posts` join `userdata` on(`userdata`.`username` = `posts`.`username`));

You can further create aVIEWbased on this query to return data:

CREATE VIEW `posts_users` AS select `posts`.`id` AS `postId`, `posts`.`username` AS `username`, `posts`.`message` AS `message`, `userdata`.`id` AS `userId`, `userdata`.`displayName` AS `displayName`, `userdata`.`email` AS `email` from (`posts` join `userdata` on(`userdata`.`username` = `posts`.`username`));

Use theSELECTstatement to query the view:

select * from posts_users order by postId

Demo:https://www.db-fiddle.com/f/tbBXvthBtwH7CKu1yjzPjQ/0

^*Foreign keys also allow updates and deletes from the parent table to be cascaded to the child tables, but this is beyond the scope of this article.