InnoDB外键难吗？
下面我有两个表
users 和 users_profiles
都是 innoDB 和排序规则：utf8_general_ci
它们如下：
users
CREATE TABLE `users` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`status` char(10) NOT NULL,
`username` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`password` char(32) NOT NULL,
`reg_date` int(11) NOT NULL,
`ip` varchar(39) DEFAULT NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `username` (`username`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
users_profiles
CREATE TABLE `users_profiles` (
`uid` int(11) NOT NULL,
`first_name` varchar(40) DEFAULT NULL,
`last_name` varchar(50) DEFAULT NULL,
`gender` char(6) DEFAULT NULL,
`website` varchar(100) DEFAULT NULL,
`msn` varchar(60) DEFAULT NULL,
`aim` varchar(60) DEFAULT NULL,
`yim` varchar(60) DEFAULT NULL,
`twitter` varchar(15) DEFAULT NULL,
UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在 phpmyadmin 中创建表后，我在 users_profiles 表上创建了一个外键，下面的代码是 phpMyAdmin 创建的。
如下：
ALTER TABLE `users_profiles`
ADD CONSTRAINT `users_profiles_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;
基本上，users_profiles.uid 是一个外键，并引用 users.uid
在 phpMyAdmin 中，我转到 插入 并填写一些示例数据，使 uid 明显自动递增。当我在用户表中插入一条记录时，我进入 users_profiles 表并注意到 users.uid 没有自动插入到 users_profiles 中，
这正常吗？
原因是，例如，当有人在表单上注册时，他们会被要求提供用户名、电子邮件和密码，我在 php 中执行查询以将该数据插入到 users 表中，我认为因为我有一个外键，它还会自动在 users_profiles 表中插入一行，其中包含用户表中的 uid，因此用户和配置文件之间存在链接。但是，当我将记录插入用户表时，users.uid 不会插入到 users_profiles 表中。
我尝试了另一个例子来看看会发生什么，由于更新和删除的级联，这个例子正如我所期望的那样工作。
如果我在users表中插入一行，然后手动将users.uid插入users_profiles.uid（它们现在已链接）并添加例如我的first_name和last_name，然后在phpmyadmin中从users表中删除用户它删除 users_profiles 表中的行。这显然应该像它应该的那样工作，因为我不希望删除用户并保留其个人资料。
这让我很困惑，因为当我创建表单并且用户注册时，他们基本上不会有个人资料，因为在注册时没有为他们创建个人资料，并且 user_profiles 表中有 users.uid （它们之间没有链接），尽管我有一个外键。
有人可以解释一下为什么它没有按我的预期工作，也许它应该像我想要的那样工作，但是出了问题或者我错过了整个要点。
更新
参考@Mark Wilkins的回复，
我现在明白你的意思了。但我不能100%确定的是：
用户注册，在用户表中创建一条记录；他们登录并访问个人资料页面，可以在其中填写并提交表格。
在处理表单时，我的想法是正确的，我需要执行以下操作：
用户填写个人资料表单并提交（他们第一次提交个人资料，因为他们是新用户），在验证数据等之后，我首先检查 uid 是否在 users 表中匹配 users_profile 表中的 uid，如果存在匹配，则使用新值更新记录（这意味着用户之前在注册时填写了配置文件，但他们没有），但如果没有找到匹配项然后从两个表中获取 uid我将执行 INSERT 查询，因为该用户尚不存在配置文件。我认为显然我会在成功登录时将用户表中的 uid 与其他数据存储在会话中，并且会话中的 uid 将是插入到 users_profiles 表中 uid 列中的 uid？这样，两个表之间就会创建一个链接，如果我现在决定删除用户，那么配置文件也将被删除。
Below I have two tables
users and users_profiles
Both are innoDB and collation: utf8_general_ci
They are as follows:
users
CREATE TABLE `users` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`status` char(10) NOT NULL,
`username` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`password` char(32) NOT NULL,
`reg_date` int(11) NOT NULL,
`ip` varchar(39) DEFAULT NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `username` (`username`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
users_profiles
CREATE TABLE `users_profiles` (
`uid` int(11) NOT NULL,
`first_name` varchar(40) DEFAULT NULL,
`last_name` varchar(50) DEFAULT NULL,
`gender` char(6) DEFAULT NULL,
`website` varchar(100) DEFAULT NULL,
`msn` varchar(60) DEFAULT NULL,
`aim` varchar(60) DEFAULT NULL,
`yim` varchar(60) DEFAULT NULL,
`twitter` varchar(15) DEFAULT NULL,
UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
After creating the tables in phpmyadmin I created a foreign key on the users_profiles table, code below is what phpMyAdmin created.
As follows:
ALTER TABLE `users_profiles`
ADD CONSTRAINT `users_profiles_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;
basically the users_profiles.uid is a foreign key and references the users.uid
In phpMyAdmin I go to insert and fill in some sample data leaving the uid obviously to auto increment. When i have inserted a record in users table I goes into the users_profiles table and notice the users.uid is not inserted automatically in the users_profiles,
Is this normal?
Reason is when someone for example registers on a form, they will be asked for username, email and password, and i do a query in php to insert that data into users table, i thought that because i have a foreign key that it would also automatically insert a row in the users_profiles table with the uid from users table so there is a link between the user and there profile. But when i insert a record into users table the users.uid is not inserted into the users_profiles table.
I tried another example to see what would happen and this one works as i would expect due to the cascade on update and delete.
If i insert a row in users table and then manually insert the users.uid into users_profiles.uid (they are now linked) and add for example my first_name and last_name then in phpmyadmin delete the user from users table it deletes the row in the users_profiles table. This works like it should obviously as i don't want a user to be deleted and have there profile remain.
This has confused me as when I do create a form and a user signs up, they essentially would not have a profile because on signup no profile is created for them with there users.uid in the user_profiles table (no link between them) although I have a foreign key.
Can some explain why it's not working as I expect, maybe it should be working like I want it to but something is wrong or I am missing the whole point otherwise.
UPDATE
In reference to reply from @Mark Wilkins
I understand what you mean now. But something I am not 100% sure on is this:
User signs up, a record is created in users table; they login and visit profile page where the can fill it in and submit the form.
On processing the form am I right in thinking I would need to do the following:
user filled in profile form and submitted (first time they submitted profile as they are a new user), after validating data etc I first check to see if the uid in the users table match a uid in the users_profile table, if there's a match then UPDATE record with new values (this would mean the user has previously filled in there profile as on signup they don't have one) but if no match is found on uid from both tables then I would perform an INSERT query because no profile yet exists for the user. I take it that obviously I would store the uid from users table in session with other data on successful login and the uid in session would be the uid that is inserted into the users_profiles table in column uid? That way a link is created between two tables and if I now decide to delete the user there profile will also be deleted to.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
外键约束不是为了创建行而设计的。它们的目的是通过强制子表中引用父表值的值实际存在于该父表中来确保数据完整性，并防止删除在子表中引用它的父行。
插入时，调用代码必须将行写入两个表（首先是用户，然后是配置文件）。
Foreign key constraints are not designed to create rows. Their purpose is to ensure data integrity by forcing that a value in a child table that references a parent table value actually exists in that parent table and prevents a parent row from being deleted that has references to it in a child table.
On insert, the calling code must write rows into the two tables (first users then profiles).
如果我正确地遵循了描述，它就会按预期工作。外键关系基本上表示给定子级必须存在父级（示例中给定 user_profile 必须存在用户）。它不需要相反的情况（用户存在 user_profile 记录）。它永远不会导致在子表上执行 INSERT。您必须将记录插入到 user_profile 表中，并且外键关系将保证它得到维护。
编辑附加OP信息：总的来说，是的，我相信这就是你想要做的事情。我对网络开发的处理还不够，不知道这个特定的过程是否正确。但无论哪种情况（无论是否已创建配置文件记录），您都需要知道要修改哪个用户。然而，我对此的看法是在创建用户记录后直接创建关联的 user_profile 记录（只需将其中的信息字段留空即可）。这样，当您去编辑它时，您就知道它存在，并且不必执行 MERGE 风格的操作。
If I followed the description correctly, it is working as expected. A foreign key relationship basically says that a parent must exist for a given child (a user must exist for a given user_profile in your example). It does not require the opposite (that a user_profile record exist for a user). It will never result in an INSERT being performed on the child table. You have to insert the record into the user_profile table and the foreign key relationship will guarantee that it is maintained.
Edit for the additional OP info: In general, yes I believe that is the thing you want to do. I have not dealt enough with web development to know if that particular process is correct. In either case, though (whether or not a profile record has been created), you will need to know which user to modify. My opinion about this, however, would be to create the associated user_profile record directly after creating the user record (just leave the informational fields empty in it). That way you know it exists when the go to edit it and you don't have to perform a MERGE style operation.