Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose I want to map a set of input values to database table fields E.g.

employeeFirstName --> emp.fName
employeeLastName  --> emp.lName
...               --> ...

In theory I can create a lookup table like this:

create table lkp( input_field varchar(50), 
                  table_name varchar(50), 
                  column_name varchar(50),
                  constraint table_name_fk foreign key (table_name, column_name) 
                             references information_schema.tables (table_name, column_name));

However, view columns can not be used as foreign key references.

Are there any other techniques for enforcing this relationship?

The RDBMS I'm using is SQL Server.

share|improve this question
1  
If you make this an indexed view then you can do that. –  Zane Feb 27 at 21:16
    
Not against that table: Cannot schema bind view 'lkp' because it references system object 'INFORMATION_SCHEMA.TABLES'. –  Michael J Swart Feb 27 at 21:28
1  
You're struggling with this because you're mixing up DML and DDL. A DDL statement could violate a foreign key constraint which is weird. I've struggled whenever I've blurred data and metadata. You're going to find there is no way to create such a foreign key. And you may run into further troubles. –  Michael J Swart Feb 27 at 21:31
    
If you really want to make this work, you're going to have to use DDL triggers. technet.microsoft.com/en-us/library/ms190989(v=sql.105).aspx –  Michael J Swart Feb 27 at 21:33
1  
I don't have time to post an answer now but if you read this this will show you how to turn that view into an indexed view. You can then create it just like a table. Keep in mind there are costs associated with this however. –  Zane Feb 27 at 22:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.