VB.Net&访问 - 设置两个引用同一个表/字段的外键 [英] VB.Net & Access - Set two foreign keys, that refer to the same table/field

问题描述

我有一个Access数据库，我想存储联系人在公司：
在这个数据库中，我有一个名为用户，我保持用户
将有权访问数据库。在名为contacts的主表中，
两个字段insert_user和contact_user是指输入联系信息的用户
和实际进行联系的用户。
我想这样做，我将不得不将两个字段设置为外键
到同一个表/字段，即users/username。

所以：
我在两个不同的字段中设置了两个外键约束（insert_user，contact_user）， / b>使用VB.Net OleDB访问数据库（请参阅下面的SQL语句）

$ pre $ AL $ TABLE联系人ADD CONSTRAINT insert_user_foreignkey FOREIGN KEY（insert_user）REFERENCES用户（用户名）
ALTER TABLE联系人ADD CONSTRAINT contact_user_foreignkey FOREIGN KEY（contact_user）REFERENCES用户（用户名）

我用MS-Access打开数据库。
我发现所有的表都没有问题（因为它们应该是这样）。

然而，当我在Access上点击关系时，我得到了所有的表格和表格名称users_1连接到contacts/contact_user（表用户只连接到contacts/insert_user）

很奇怪的是在Table访问的视图我没有看到这个表，但只在关系视图。

问题

• 我认为是正确的（设置2个外键，指的是同一个表/字段）？
  
• 什么是这个表users_1？


• 我的数据库能工作吗？
？

>

解决方案

这是Access多次使用表时建立关系的方式。如果您添加了另一个关系，那么这将适用于users_2等。这只是一个别名，不是一个新表。

I have an Access database where I want to store the contacts in a company: In this database I have a table named "users" where I keep the users which will have access to the database. In the main table, named "contacts", two of my fields "insert_user" and "contact_user" refer to the user who entered the contact information and the user who actually made the contact. The way I am thinking it, I will have to set both of the fields as foreign keys to the same table/field, namely "users"/"username".

So: I set two foreign key constrains in two different fields ("insert_user", "contact_user"), which refer to the same table/field ("users"/"username") in an Access database, using VB.Net OleDB (See the SQL Statement below)

ALTER TABLE contacts ADD CONSTRAINT insert_user_foreignkey FOREIGN KEY (insert_user) REFERENCES users (username)
ALTER TABLE contacts ADD CONSTRAINT contact_user_foreignkey FOREIGN KEY (contact_user) REFERENCES users (username)

I open the database with MS-Access. I see that all the tables are fine (as they should be).

However when I click on relationships at Access I get all my tables plus a table named users_1 connected to "contacts"/"contact_user" (the table users is only connected to "contacts"/"insert_user")

It is very strange that in the Table View of Access I don't see this table, but only in the Relationships View.

Questions

• Is my thinking right (to set 2 foreign keys, that refer to the same table/field)?
• What is this table "users_1"?
• Will my database work or am I doing something wrong?

解决方案

This is the way that Access sets up relationships when a table is used more than once. If you added another relationship, it would be for users_2 and so on. This is only an alias, not a new table.

这篇关于VB.Net&访问 - 设置两个引用同一个表/字段的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！