创建没有任何外键的数据库实体图
我需要创建 MSSQL 2005 数据库的实体图。
关系是通过一致地使用主键来定义的，但任何地方都没有外键。 我尝试了Microsoft Visio的“逆向工程”功能，当然由于缺少外键而失败了。
因此，我需要一个在绘制关系时不仅仅依赖于外键的图表工具。
I need to create an entity diagram of a MSSQL 2005 database.
Relationships are defined by using primary keys consistently, but there are no foreign keys anywhere. I tried Microsoft Visio's "Reverse Engineer" function, which of course failed due to the lack of foreign keys.
Therefore, I need a diagram tool which doesn't solely rely on foreign keys when drawing relationships.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
创建一个脚本来创建正确的外键关系，运行图表工具，然后运行第二个脚本来删除外键。
这将允许您使用您的工具而不会过多地破坏数据库。 如果第一个脚本失败，那么您就会知道数据也有问题。
[编辑] 如果外键列的命名有一些规则，您可以使用脚本语言为您生成 SQL。
如果这也失败了，任何设计工具都应该允许您创建缺失的关系。 这意味着您可能会遇到数据不一致的情况。 这里的解决方案是制作表定义的快照并在专用数据库服务器上重新创建数据库（没有数据）。 在那里，您可以随心所欲地修改设计，而不会破坏原始系统。
当您完成修复设计后，您可以提取命令来创建外键并将其应用到真实系统 - 如果您愿意的话。 这样，您就可以感受到数据库中的混乱情况有多大。 如果没有，那么您可以简单地保留新副本，在那里进行任何设计更改，并且在检查后，您可以将更改迁移到生产数据库。
在我自己的系统中，我总是有脚本来快速创建当前开发和生产数据库的克隆。 通常，我使用 Derby 或 HSQL 等嵌入式数据库。 但是，如果您向进程添加过滤器，则可以在 DDL 文件中使用
$(SCHEMA)并将相同的数据库安装到同一服务器上的不同架构中。 我们在数据迁移项目中使用它并取得了巨大成功，我们将每个迁移测试的结果保存在一个新架构中（TABLE_DATE_XX，其中XX是一个两位数，因此您每天可以创建多个测试）。这使我们能够验证各种修复、比较两个迁移等。由于整个过程 100% 自动化，因此创建新模式比修复现有模式更便宜。
Create a script which creates the correct foreign key relations, run the diagram tool and then run a second script which drops the foreign keys.
This would allow you to use your tool without disrupting the database too much. If the first script fails, then you'll know that something is wrong with the data, too.
[EDIT] If there is some rule how foreign key columns are named, you can use a scripting language to generate the SQL for you.
If that also fails, any design tool should allow you to create the missing relations. That means you'll probably run into data inconsistencies. The solution here is to make a snapshot of the table definitions and recreate the database (without the data) on a private database server. There, you can mess with the design as much as you want without disrupting the original system.
When you're done with fixing the design, you can extract the commands to create the foreign keys and apply that to the real system -- if you like. That way, you can get a feeling how big the mess in the database already is. If not, then you can simply keep the new copy around, make any design changes there and, after they have been checked, you can migrate the changes to the production database.
In my own systems, I always have scripts to quickly create a clone of the current development and production database. Usually, I use an embedded database like Derby or HSQL. But if you add a filter to the process, you can use
$(SCHEMA)in the DDL files and install the same database into different schemas on the same server. We used that with great success during a data migration project where we would save the results of each migration test in a new schema (TABLE_DATE_XXwhereXXis a two-digit number so you can create more than one test per day).That allowed us to verify the various fixes, compare two migrations, etc. Since the whole process was 100% automated, it became more cheap to create a new schema than to fix an existing schema.