数据库设计,哪个表有外键
我有一个表USER
(USER_ID、PASSWORD、NAME,...)和一个用于用户的表ACCESS_ROLES
,每个用户可以有一个ACCESS_ROLE
>(一对一)。
哪个表有外键?
我会将 USER_ID
放入 ACCESS_ROLES
表中。有没有最佳实践方法?
I have an Table USER
(USER_ID, PASSWORD, NAME, ...) and an Table ACCESS_ROLES
for the Users, every user can have one ACCESS_ROLE
(ONE-TO-ONE).
Which table has the Foreign Key?
I would put the USER_ID
into ACCESS_ROLES
table. Is there any best practice approach?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
如果您有一组固定的访问角色和任意数量的用户,其中为用户分配了一个且仅一个访问角色,并且可以为任意数量的用户分配给定的访问规则[这就是我解释您的问题的方式],那么您可以在 USERS 表中放置一个类似“AccessRoleId”的列,并将外键约束添加到 ACCESS_ROLES 中。
If you have a fixed set of access roles and any number of users, where a user is assigned one and only one access role, and any number of users can be assigned a given access rule [this is how I interpret your question], then you would put a column like "AccessRoleId" in your USERS table and add a foreign key constraint into ACCESS_ROLES.
由于你们将建立一对一的关系,菲利普·凯利建议的解决方案更好。只需在
USER
中添加一个名为access_role_id
的新列,然后在USER
表上执行外键操作,如下所示:不要添加
USER_ID按照您的建议,将
放入ACCESS_ROLES
表中。Since you will be having a one-to-one relationship, the solution suggested by Philip Kelley is better. Just add a new column in
USER
calledaccess_role_id
and then do the foreign key on theUSER
table like this:Do not add
USER_ID
intoACCESS_ROLES
table, as you suggested.那么:
注意:外键“父”列必须被限制为唯一。如果每个 ACCESSROLE 有多个用户,则 FK必须
在 SQL Server 中从 USERS 到 ACCESSROLES
Then:
Note: the foreign key "parent" column(s) must have be constrainted unique. If you have multiple users per ACCESSROLE, the FK must be from USERS to ACCESSROLES
In SQL Server
如果您有一对一映射,我会将外键放入您大多数时间都会查询的表中。
If you have a One-To-One mapping, I would put the foreign key into the table that you will be querying most of the time.
您可以将外键添加到 ACCESS_ROLES
和以下内容以强制执行一对一映射
You can add a foreign key to ACCESS_ROLES
and the following to enforce one to one mapping
是的,有!
与
主键
一样,外键
也是一种施加在一个对象上的约束
或表中的多个列。外键
在键列和另一个表中的相关列之间建立链接。 (您还可以将外键列链接到同一表中的列。)包含外键的表被视为子表,外键引用的表被视为父表。
关键点
外键
必须引用主键
或唯一约束
,尽管该引用可以位于同一个桌子上或另一张桌子上。主键
不同,外键
列可以包含NULL
值。Yes, there is!
Like a
primary key
, aforeign key
is also a type ofconstraint
placed on one or more columns in a table.The
foreign key
establishes a link between the key columns and related columns in another table. (You can also link the foreign key columns to columns within the same table.)The table that contains the foreign key is considered the child table, and the table that the foreign key references is the parent table.
Key Points
foreign key
must reference aprimary key
orunique constraint
, although that reference can be on the same table or on a different table.foreign key
must also have the same number of columns as the number of columns in the referenced constraint, and the data types must match between corresponding columns.Primary key
,Foreign key
columns can containNULL
values.不确定我是否理解这个问题:我认为 access_table 应该有外键。
Not sure I understand the question or not: I think the access_table should have the Foreign Key.