保证子记录要么在一个表中，要么在另一个表中，但不能同时在两个表中？
我有一张桌子，里面有两个子桌子。对于父表中的每条记录，我希望子表之一中只有一条记录 - 不是每个记录都有一个，也不是没有。我该如何定义呢？
这是背景故事。请随意批评此实现，但请回答上面的问题，因为这不是我唯一一次遇到它：
我有一个数据库，其中保存与用户调查相关的数据。它最初设计为使用一种用于启动调查的身份验证方法。从那时起，要求发生了变化，现在人们可以通过两种不同的方式登录开始调查。
最初，我在调查表的一列中捕获了身份验证令牌。由于需求发生了变化，我想在身份验证中捕获其他三个数据位。因此，对于调查表中的每条记录，我要么拥有一个令牌，要么拥有一组三个令牌。所有这四个都是不同的类型，所以我的想法是，不要有四个列，其中一个将为空，或者三个将为空（或者更糟糕的是，这两种情况中的任何一个的糟糕混搭），我将有两个子表，一个用于保存单个身份验证令牌，另一个用于保存三个身份验证令牌。问题是，我不知道如何在 DDL 中定义它。
我正在使用 MySQL，所以也许 MySQL 未实现的某个功能可以让我执行此操作。
I have a table with two child tables. For each record in the parent table, I want one and only one record in one of the child tables -- not one in each, not none. How to I define that?
Here's the backstory. Feel free to criticize this implementation, but please answer the question above, because this isn't the only time I've encountered it:
I have a database that holds data pertaining to user surveys. It was originally designed with one authentication method for starting a survey. Since then, requirements have changed, and now there are two different ways someone could sign on to start a survey.
Originally I captured the authentication token in a column in the survey table. Since requirements changed, there are three other bits of data that I want to capture in authentication. So for each record in the survey table, I'm either going to have one token, or a set of three. All four of these are of different types, so my thought was, instead of having four columns where either one is going to be null, or three are going to be null ( or even worse, a bad mashup of either of those scenarios ), I would have two child tables, one for holding the single authentication token, the other for holding the three. Problem is, I don't know offhand how to define that in DDL.
I'm using MySQL, so maybe there's a feature that MySQL doesn't implement that lets me do this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
有多种方法可以实现此
布尔标志逻辑。
您可以在 ParentTable 中拥有一个名为 ChildTableDataInserted 的布尔标志，并且如果在任何一个表中插入行，则可以将其更新为 TRUE。仅当 ChildTableDataInserted 为 False 时才插入子行。这当然取决于使用您创建的存储过程进行的所有插入，并且很容易被违反，但数据库的开销很小。
触发器
在向两个子表插入数据时放置BEFORE INSERT，如果满足故障条件，则可以引发插入错误。
我个人会避免使用触发器，除非我绝对没有其他选择，这似乎是实现触发器的好地方。
在此处阅读有关触发器的更多信息
http://dev.mysql.com /doc/refman/5.0/en/create-trigger.html
There are multiple ways you can implement this
Boolean Flag Logic
You can have a boolean flag in ParentTable called ChildTableDataInserted and you can update this to TRUE if row is inserted in any one table. Only insert into a child row if the ChildTableDataInserted is False. This of-course is dependent on all inserts using the stored procedures that you create and can easily be violated, but overhead on the database is small.
Triggers
Put a BEFORE INSERT on the data insert to both of the child tables, and if your fault condition is met, you can raise an error for the insert.
I personally avoid triggers unless I have absolutely no other option and this seems like a good place to implement triggers.
Read more on triggers here
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html