您可以与系统表/视图建立关系吗？
是否可以建立从用户表到系统表视图的关系？ 为了提供上下文，我希望将一个表中的列的值限制为另一个表的列名称，这似乎最容易做到通过查看包含第二个表的列名的系统视图。
因此，使用经典示例，如果我有一个客户表（名字姓氏），我想创建另一个表，其中包含“customerAttribute”列，该列只能是“FirstName”或“LastName”。 为了保持这种动态性，如果“customerAttribute”列实际上是系统视图中存储 Customer 表中的列名称的外键，那就太好了； 这样，我不必担心数据完整性问题，因为在实际的 Customers 表中添加、删除和重命名列。
我没有看到在 SQL Server 中创建这种关系的简单方法，所以我想知道是否应该禁止使用系统表和/或视图来搞乱/创建与系统表和/或视图的关系。
谢谢！
PS我问这个问题是为了帮助我解决另一个问题我已经发布在SO上。
编辑：即使您无法直接与系统视图建立关系，也许您可以创建一个视图，将查询返回到系统视图（以获取列名称），然后与该视图建立关系...我会现在就试试吧。
Is it possible to have a relationship from a user table to a system table view? To give context, I would like the values of a column in one of my tables to be restricted to the column names of another one of my tables, which seems easiest to do by going through the system view that houses the column names of the second table.
So, using the classic example, if I had a table of Customers (FirstName LastName), I would like to create another table that had a column "customerAttribute" which could only be "FirstName" or "LastName." To keep this dynamic, it would be nice if the "customerAttribute" column was actually a foreign key from the system view that stores the names of the columns in the Customer table; that way, I don't have to worry about data integrity issues as columns are added, deleted, and renamed in the actual Customers table.
I didn't see an easy way to create this relationship in SQL Server, so I'm wondering if messing with/creating relationships with system tables and/or views is a major no-no.
Thanks!
P.S. I ask this question to help me solve another problem I've posted on SO.
Edit: Even if you cannot directly make a relationship to a system view, perhaps you could create a view that returned the query to the system view (to get the column names) and then made a relationship to that view... I'll try that now.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
显然没有什么可以阻止您创建引用系统表列的用户表列。 所以显而易见的答案是肯定的。 要回答您真正的问题，有必要问一个不同的问题，即：将元数据存储在用户表中是个好主意吗？
如果我从数据管理纯粹主义者的角度来回答，我会说这几乎总是一个坏主意。 然而，实际上，我已经做到了，尽管我并不为此感到自豪。 通过混合数据和元数据可以获得一些结果，如果不混合它们几乎不可能实现。
您面临的风险是，最终您将得到一个数据库，该数据库无法按照对主题专家有意义的属性进行记录。 换句话说，您的数据库只能由您自己和您的极客同伴使用。 有时这是可以接受的风险。 有时并非如此。
There's obviously nothing to prevent you from creating a user table column that references a system table column. So the obvious answer is yes. To answer your real question it's necessary to ask a different question, namely: is it a good idea to store metadata in user tables?
If I were to answer from a data management purists point of view, I'd say that this is nearly always a bad idea. As a practical matter, however, I've done it even though I'm not proud of it. There are some results you can get by blending data and metadata that are nearly impossible to acheive without blending them.
The risk you run is that eventually you'll end up with a database that can't be documented in terms of attributes that make sense to subject matter experts. In other words, your database will only be useable by yourself and your fellow geeks. Sometimes that's an acceptable risk. Sometimes it isn't.
依赖系统表总是一个坏主意，因为 MS 可以在没有警告的情况下更改它们。 为什么不使用信息架构视图，哪些旨在便携并支持需要查询元数据的应用程序？
Having dependencies on the system tables is always a bad idea, as MS can change them without warning. Why not use the information schema views, which are intended to be portable and support applications that need to query metadata?
我不确定这是否可能，但如果是的话，那么您肯定正在踏上危险的土地......您将如何重命名列？ 您将如何移动列？ 您将如何删除列？ 这种类型的事情可能会在 SSMS 内部造成很大的破坏。 这种类型的事情最好使用触发器或在代码逻辑中处理。
I'm not sure if it is even possible, but if it is then you are certainly treading on dangerous ground... how would you rename a column? How would you move a column? How would you delete columns? This type of thing would probably break a lot inside SSMS. This type of thing is better handled with a trigger or in your code logic.