Moving from a table to a view
I have a table that contains user info. It has a primary key of userid. I have foreign key constraints in various other tables that reference the userid. These tables and database are for new applications.
Now, there are legacy apps also use a table of users. I need to make the legacy apps and new apps use the same list of users. The legacy apps use one database and the new apps use another, though on the same server. So, I've created a view in the new apps database that pulls in the necessary user columns from the legacy apps database.
Now, prior to this, and for development purposes only, I had a temp user table in the new apps database, and I had created foreign key constraints. Now, if I go with the view idea that I describe above, and remove the users table in the new apps database, I lose referential integrity enforced by the foreign keys, correct?
Questions: Is this a reasonable approach, to get the two sets of app to use the same set of user data? If I name the view the same as the user table and use the same column names, will this change be transparent? Is the loss of referential integrity a small price to pay? Any suggestions on how to better handle this?
Now, there are legacy apps also use a table of users. I need to make the legacy apps and new apps use the same list of users. The legacy apps use one database and the new apps use another, though on the same server. So, I've created a view in the new apps database that pulls in the necessary user columns from the legacy apps database.
Now, prior to this, and for development purposes only, I had a temp user table in the new apps database, and I had created foreign key constraints. Now, if I go with the view idea that I describe above, and remove the users table in the new apps database, I lose referential integrity enforced by the foreign keys, correct?
Questions: Is this a reasonable approach, to get the two sets of app to use the same set of user data? If I name the view the same as the user table and use the same column names, will this change be transparent? Is the loss of referential integrity a small price to pay? Any suggestions on how to better handle this?
Bob Learned
I am not sure that I completely understand your question. Referential integrity is enforced at the table level, and I don't quite understand what changes you would be making to the tables.
ASKER
I would lose the Referential integrity for the userid's in the new database because I'd now create a view in the new database that would pull in the data from the old database.
The new database has other tables that currently have foreign keys to the temporary user table - which would go now go away and be replaced by the new view.
Did I articulate this properly?
The new database has other tables that currently have foreign keys to the temporary user table - which would go now go away and be replaced by the new view.
Did I articulate this properly?
Are you saying that you have a linked server, and the view would be referencing data from both databases, without foreign key relationships?
ASKER
Not linked. Two databases on the same server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!