question

coolspeedway avatar image
coolspeedway asked

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
triggertablesforeign-keyautomationdata-type
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I forgot to precise I am using an instance of SQL server 2014
0 Likes 0 ·
Are you going to allow the application to add a city that doesn't exist in the City table?
0 Likes 0 ·
I would like to since I don't know if cities that are not in the table will be added in the future.
0 Likes 0 ·
So far I managed to create an INSTEAD of trigger and the keys seems to match properly. However I still don't know how to dynamically create new cities from an insert.
0 Likes 0 ·

1 Answer

· Write an Answer
sjimmo avatar image
sjimmo answered
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.
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So far I managed to take care of everything in terms of inserting data. However I am not really familiar with views but from what I understood they could really help me to read my data without having to change what data to read but rather id read the new view instead of the old table ? Considering the new view would be just like the old table.
0 Likes 0 ·
would suggest the following: Create a table for zip codes. You can download free zip code databases off the internet, including one from the Postal Service. The, instead of storing the city and state information multiple times, store the zipcode. Then in your select you can join on the zipcode table to retrieve the city/state info. Now you have reduced even further the amount of data stored from say 35 to 5 bytes. As for your cities table, you could do a select to see if the city exists, and if not then perform an insert. Problem with this is that if there is a typo on a city name, it would show up as a false, and you would insert a city which is spelled wrong.
0 Likes 0 ·
As for your views, you can use them for everything. Some places do not allow access directly to the tables, but do DML through views and stored procedures.
0 Likes 0 ·
So if I understand correctly a view is only something that mimics a table by reading info in a table(or multiple table in my case) and that doesn't use space on my database ? I currently have automated reports with Crystal Report that read certain data in my old table. Changing the old table for my new view would require little action to change how my reports get their info?
0 Likes 0 ·
Pretty much. Take your original tables and rename them to for example. Then create a view with the name . The view will now take the place of the original table. The fields will be there, etc. This will allow everything to continue working as it did. Now you create a new view which covers and , you cannot do an insert into multiple tables through a view. This is where the instead of insert filter comes into play. It adds flexability to the database, and in some cases will allow a poorly designed database to be fixed without completely rebuilding it. A case I am working on now using the same principal. A 3rd party software changed a code from an INT to a varchar. We have a lot of code written on that code. So we are doing the same thing. Creating a 2nd table with a view so that we can have a mapping table to be able to use the INT still. Thus the view won't work with the 2nd table so we are using a view with an instead of trigger on it.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.