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.

  • 1
    If you make this an indexed view then you can do that. – Zane Feb 27 '14 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 '14 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 '14 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 '14 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 '14 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.