我一直在努力弄清楚为什么我似乎无法添加用户。这是数据库设置问题吗?还是只是语法问题?需要一些指导。
当试图简单地插入现有的user_id到和现有的room_id时,我一直在获得
查询:
INSERT INTO group_users (room_id, user_id) VALUES(1, 3);
ERROR: Cannot add or update a child row: a foreign key constraint fails
数据库中已经存在用户id和房间id。想知道我需要做什么。
这里参考的是数据库模式:
CREATE TABLE `groups` (
`room_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`room_name` varchar(60) NOT NULL,
PRIMARY KEY (`room_id`),
UNIQUE KEY `room_name` (`room_name`)
)ENGINE=InnoDB;
-- Chatapp.users definition
CREATE TABLE `users` (
`user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(60) NOT NULL,
`language` varchar(32) DEFAULT 'english',
`SocketID` varchar(42) DEFAULT NULL,
`email` varchar(60) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`registered_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`confirmed_email` tinyint(1) NOT NULL DEFAULT '0',
`confirmed_on` datetime DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`)
)ENGINE=InnoDB;
-- Chatapp.group_users definition
CREATE TABLE `group_users` (
`room_id` bigint(20) unsigned NOT NULL,
`user_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`room_id`,`user_id`),
KEY `group_users_fk2` (`user_id`),
CONSTRAINT `group_users_fk1` FOREIGN KEY (`room_id`) REFERENCES `groups` (`room_id`),
CONSTRAINT `group_users_fk2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB;
-- Chatapp.messages definition
CREATE TABLE `messages` (
`message_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned DEFAULT NULL,
`message` mediumtext COLLATE utf8mb4_bin,
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`message_id`),
KEY `messages_fk1` (`user_id`),
CONSTRAINT `messages_fk1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
如果我将group_id
更改为room_id
,它现在可以正常工作:
CREATE TABLE `group_users` (
`room_id` bigint(20) unsigned NOT NULL,
`user_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`room_id`,`user_id`),
KEY `group_users_fk2` (`user_id`),
CONSTRAINT `group_users_fk1` FOREIGN KEY (`room_id`) REFERENCES `groups` (`room_id`),
CONSTRAINT `group_users_fk2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `users` (`user_id`, `username`, `language`, `SocketID`, `email`, `password`, `registered_on`, `confirmed_email`, `confirmed_on`) VALUES (NULL, 'pavel', 'english', NULL, NULL, NULL, CURRENT_TIMESTAMP, '0', NULL);
INSERT INTO `users` (`user_id`, `username`, `language`, `SocketID`, `email`, `password`, `registered_on`, `confirmed_email`, `confirmed_on`) VALUES (NULL, 'bob', 'english', NULL, NULL, NULL, CURRENT_TIMESTAMP, '0', NULL);
INSERT INTO `groups` (`room_id`, `room_name`) VALUES (NULL, 'room1');
INSERT INTO `groups` (`room_id`, `room_name`) VALUES (NULL, 'room2');
INSERT INTO `group_users` (`room_id`, `user_id`) VALUES ('1', '2');
INSERT INTO `group_users` (`room_id`, `user_id`) VALUES ('1', '1');
结果:
+---------+---------+
| room_id | user_id |
+---------+---------+
| 1 | 1 |
| 1 | 2 |
+---------+---------+
rooms
与groups
相同吗?请注意,在groups
-表中没有您的约束所建议的room_id
。