I have 2 user tables - one for employees and one for temp workers. Both have userid as the primary key. I would like to ensure that a new record on one does not match the userid on the other. I can do this in my php code; but I'm wondering if there is a restraint in MySQL that will help ensure this. Ex:
Table 1
userid - name - email - ...
bsmith - Bob Smith - ...
我有2个用户表 - 一个用于员工,一个用于临时工。两者都有userid作为主键。我想确保一个上的新记录与另一个上的用户ID不匹配。我可以在我的PHP代码中执行此操作;但我想知道MySQL中是否存在限制以确保这一点。例如:表1 userid - 名称 - 电子邮件 - ... bsmith - Bob Smith - ...
Table 2
userid - name - contracthouse - ...
jallen - Joe Allen - ...
表2 userid - name - contracthouse - ... jallen - Joe Allen - ...
If I insert a new contract employee into Table 2; is there a MySQL constraint that would keep me from entering "bsmith" (this is what I would want); or is that only achievable in the php code?
如果我在表2中插入新的合同员工;是否存在一个MySQL约束,使我无法进入“bsmith”(这就是我想要的);或者只能在php代码中实现?
Thank you
1 个解决方案
#1
1
You could go about this in a couple different ways. The first would be to add triggers to each table that on insert checks to see if the record being inserted exists in the other table. If it does you can throw an exception.
你可以通过几种不同的方式解决这个问题。第一种方法是在插入检查时为每个表添加触发器,以查看正在插入的记录是否存在于另一个表中。如果是,你可以抛出异常。
The second, and probably the better way to do this would be to redesign your tables. You should only have one table holding this data and you can add a column called user_role or something that FKs to a lookup table containing values for the role of the user: Full Time, Part Time, Temporary, etc...
第二种,也许更好的方法是重新设计你的表。您应该只有一个包含此数据的表,您可以将一个名为user_role的列或FK的列添加到包含用户角色值的查找表中:Full Time,Part Time,Temporary等...
You can have a further child table that contains extended information about the user based on their role type, or some other such data.
您可以拥有另一个子表,其中包含有关用户的角色类型或其他一些此类数据的扩展信息。
#1
1
You could go about this in a couple different ways. The first would be to add triggers to each table that on insert checks to see if the record being inserted exists in the other table. If it does you can throw an exception.
你可以通过几种不同的方式解决这个问题。第一种方法是在插入检查时为每个表添加触发器,以查看正在插入的记录是否存在于另一个表中。如果是,你可以抛出异常。
The second, and probably the better way to do this would be to redesign your tables. You should only have one table holding this data and you can add a column called user_role or something that FKs to a lookup table containing values for the role of the user: Full Time, Part Time, Temporary, etc...
第二种,也许更好的方法是重新设计你的表。您应该只有一个包含此数据的表,您可以将一个名为user_role的列或FK的列添加到包含用户角色值的查找表中:Full Time,Part Time,Temporary等...
You can have a further child table that contains extended information about the user based on their role type, or some other such data.
您可以拥有另一个子表,其中包含有关用户的角色类型或其他一些此类数据的扩展信息。