Adding foreign Key constraint without changing entry type.
Hi, I currently have a table which has a lot of redundancy in it. I was looking for a way to gain space by creating new tables for my columns which were redundant. The data in my tables is entered automatically and therefor I would like to change that part as little as possible. For instance, I only had table customers before, which contained lots of field including a city field that repeat itself a lot of time. I would like to replace the city field by a foreign key which would be a small int and that point toward my new City table. I would like to create a trigger that check the city on entry and returns the foreign key of the city table instead. Which would allow me to keep my entry type a varchar and still gain space in my table. Thanks
Congratulations, it sounds like you are on your way to database normalization. There are many good articles and books written on the subject. Here is a link to one of them that will aid you in your endeavor.
http://www.sqlservercentral.com/blogs/abhijit_desai/2010/09/07/noramlization/ The easiest way in order to do what you want to and not redo your import will be to create the new tables and populate the data. Then use a view to load the data, with an instead of insert trigger where you will be able to break up the data and send different pieces to different tables. There is a very good example on doing this at the codeacademy website:
http://www.codeproject.com/Articles/236425/How-to-insert-data-using-SQL-Views-created-using-m Good luck. It isn't an easy undertaking, but you will learn a lot on database architecture, views and triggers plus a lot of T-SQL.