MS Access DDL：在关系视图中显示外键引用
使用 ADODB 连接对象，我能够将 SQL DDL 与 MS Access 一起使用（高兴！）。奇怪的是，有时声明的外键引用会显示在 Access 的关系视图中 - 这对于可视化非常有用，并且可以打印出来以显示利益相关者 - 但有时却不会。例如，我创建一个Employees 表和一个Dep_Policy 表，其中包含对Employees 的外键引用（来自Ramakrishnan 书中的示例）。这显示在关系视图中。我现在使用相同的 DDL 创建两个新表，但将名称更改为 Cat 和 Dog，作为测试。关系视图中仅显示狗，而不显示猫。代码如下：
Sub createTestSchema()
Dim cnn1 As ADODB.Connection
Dim cmd1 As ADODB.Command
Set cnn1 = CurrentProject.Connection
Set cmd1 = New ADODB.Command
Dim sqlArr As ArrayList
Set sqlArr = New ArrayList
sqlArr.Add ("CREATE TABLE Employees(ssn integer identity(0,1), name text(100), lot text(50), primary key (ssn))")
sqlArr.Add ("CREATE TABLE Dep_Policy(pname text(20), age integer, cost currency, ssn integer, primary key (pname, ssn)," & _
"FOREIGN KEY (ssn) references Employees(ssn) ON DELETE CASCADE)")
sqlArr.Add ("CREATE TABLE Cat(ssn integer identity(0,1), name text(100), lot text(50), primary key (ssn))")
sqlArr.Add ("CREATE TABLE Dog(pname text(20), age integer, cost currency, ssn integer, primary key (pname, ssn)," & _
"FOREIGN KEY (ssn) references Cat(ssn) ON DELETE CASCADE)")
With cmd1
.ActiveConnection = cnn1
.CommandType = adCmdText
Dim i As Integer
For i = 0 To sqlArr.size - 1
.CommandText = sqlArr.GetItem(i)
.Execute
Next
End With
End Sub
如何确保外键引用显示在关系视图中？对于此测试模式，大多数表都会出现，但在我的实际模式中很少出现。使用 MS Access 是客户的要求。 （顺便说一句，ArrayList是一个自定义类）
Using an ADODB connection object, I am able to use SQL DDL with MS Access (joy!). The odd thing is that sometimes declared foreign key references show up in Access' Relationship View - this would be great for visualization, and to print off to show stakeholders - but sometimes it doesn't. For example, I create an Employees table, and a Dep_Policy table with a foreign key reference to Employees (example from Ramakrishnan book). This shows in the relationship view. I now create two new tables using the same DDL, but change the names to Cat and Dog, as a test. Only Dog shows in the Relationship view, not Cat. Here's the code:
Sub createTestSchema()
Dim cnn1 As ADODB.Connection
Dim cmd1 As ADODB.Command
Set cnn1 = CurrentProject.Connection
Set cmd1 = New ADODB.Command
Dim sqlArr As ArrayList
Set sqlArr = New ArrayList
sqlArr.Add ("CREATE TABLE Employees(ssn integer identity(0,1), name text(100), lot text(50), primary key (ssn))")
sqlArr.Add ("CREATE TABLE Dep_Policy(pname text(20), age integer, cost currency, ssn integer, primary key (pname, ssn)," & _
"FOREIGN KEY (ssn) references Employees(ssn) ON DELETE CASCADE)")
sqlArr.Add ("CREATE TABLE Cat(ssn integer identity(0,1), name text(100), lot text(50), primary key (ssn))")
sqlArr.Add ("CREATE TABLE Dog(pname text(20), age integer, cost currency, ssn integer, primary key (pname, ssn)," & _
"FOREIGN KEY (ssn) references Cat(ssn) ON DELETE CASCADE)")
With cmd1
.ActiveConnection = cnn1
.CommandType = adCmdText
Dim i As Integer
For i = 0 To sqlArr.size - 1
.CommandText = sqlArr.GetItem(i)
.Execute
Next
End With
End Sub
How do I ensure that the foreign key references show up in the Relationship view? For this test schema, most tables appear but in my actual schema few appear. Using MS Access is a client requirement. (btw ArrayList is a custom class)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在早期版本中，您需要右键单击“关系”窗口中的工作区，然后选择“显示全部”。
显示所有表及其关系不是（不是吗？）默认设置，因为在拥有 500 个表的数据库中，图表几乎不可读。事实上，在大多数数据库中，自动显示所有表和关系会导致图表不可读。需要手动移动一些东西，并且通常一次只选择模式中密切相关的部分，才能制作可读的图表。
In earlier versions, you'd right-click the workspace in the "Relationships" window, and select "Show all".
Showing all tables and their relationships wasn't (isn't?) the default, because in a database that has 500 tables, the diagram would be mostly unreadable. In fact, in most databases, automatically showing all tables and relationships makes an unreadable diagram. It takes a fair bit of moving things around manually, and often selecting just closely related parts of a schema at a time, to make a readable diagram.
去年这个时候我在这里问了这个同样的问题。
简而言之，您必须
在数据库中运行以确保图表根据您通过 DDL 建立的关系进行更新。
I asked this same question here this time last year.
In brief, you must run
in the database to ensure that the diagram is updated with the relationships you established via DDL.