Extract data from another table in MYSQL database, making changes to existing columns

Question

I have a MYSQL database (in PHPMyAdmin) with two tables users and posts. Both tables have a username column. I want to modify the username column in the posts table so that it extracts the username data from the users table, that is, the posts table The data is automatically updated from the users table and is referenced during any updates to the users table.

I initially thought I could use foreign keys to achieve this functionality, but if I understand correctly, foreign keys are only associated with the primary key in the parent table, right?

I get an error saying the following syntax is incorrect, although it doesn't give any hints/solutions:

ALTER TABLE posts
MODIFY COLUMN username VARCHAR(55) NOT NULL
REFERENCES users(username) ON UPDATE CASCADE

How to modify an existing column so that it references/uses data from a column in a different table in the database?

The username columns in the two tables have the same type, size and attributes, that is, VARCHAR(55) NOT NULL, and the innoDB storage engine is used.

Answer 1

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, the JOIN statement 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 can JOIN the userdata table with the posts table 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 a VIEW based 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 the SELECT statement 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.