Can you have relationships to system tables/views?

Question

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.

Answer 1

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.

Answer 2

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?

Answer 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.