Skip to main content
A
Anonymous
November 2, 2010
Question

Question on Creating SQL Server 2K Relationship

  • November 2, 2010
  • 1 reply
  • 813 views

I have a rather large table with several dozen fields, two of which are "location" and "goal."   Most of the goals are already populated.

We now realize we need to create another table, a master "goals table" that contains just the "location" and the "goal."  I want to set this up, so that when you update a goal in the master table, it automatically updates the goal in the big table.

I know how to create a relationship between the two tables, on "location" and "goal."  But the big table has already been created, and has the goals already populated.

My question is, when I create the new, 2-column master goals table, and the relationship, will it INITIALLY, and automatically do an UPDATE on the goals in the big table?  Or does this have to be done initially, when both tables are first created?

I need for the goals in the big table to be updated as soon as I create the master goals table, and then continue to change in the future, whenever the goals are changed in the master goals table.

Will simply creating a relationship do this?  Or do I have to do something else, (like run an update query) to "force" the goals in the big table to automatically UPDATE, and then stay updated, each time a goal in the master goals table is changed?  My SQL Server book doesn't tell you this.  It only explains how it works when you FIRST create the tables, and BEFORE you populate the goals in either table.

I am getting ready to experiment with this on a backup copy of the table.  But was hoping to find out, before moving forward.  Thanks for any help/advice.

Gary

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
November 2, 2010

I'm not sure why you would be creating one table instead of two.  Separate tables for goals and locations might make more sense.  But, it's your application.

I suggest that you do the following in the order specified.

1.  Create your orders_goals table.  Make sure there is a third column to act as the primary key.  Add a single row which will act as your default.

2.  Add a column to your big table.  Give it a foreign key reference to your new table.  Give it a default value.

3.  Populate your new table.

4.  Rewrite any queries that populate or select these fields from your big table.

5.  Update the new column in your big table.

6.  Drop the existing columns in your big table.

A
Anonymous
November 4, 2010

Dan, thanks for the prompt answer.  I think I should have simplified my question, and explained it a little better.  I'm just looking for the answer to a few simple questions, which is whether or not, WHAT I WANT TO DO, can be done?  If so, I'll do the work to make it happen (learning as I go, thanks for your suggestions).  Let me try one more time on the question, with a brief description of problem first:

1.  My dept has been using 1 big table that stores monthly performance metrics, along with monthly goals, for every location.  The staff has been going into the big table, and updating monthly goals manually.

2.  Someone on my team, already created a master goals table, that contains the "metric", each of which has a goal.  This goals table stores the metric (unique primary key), along with a monthly goal (1 column for the month), the store location ID number, and of course, the goal.

By the way, we also have a 3rd table, called the master "metric" table, which stores all the metrics being measured, including a metric code (unique primary key) and official metric name.  The BIG table has both the "metric", the "metric code", the location, the month, AND the goal, (among other columns).

Lastly, the BIG table has one primary key, called ID, which is just a unique identifier number, in the first column.

MY QUESTION IS THIS:  Just given the above, without creating or modifying the existing tables, IS IT POSSIBLE,

USING NOTHING BUT RELATIONSHIPS, TO AUTOMATICALLY UPDATE THE GOALS IN THE BIG GOALS TABLE, BY UPDATING THE GOALS IN THE MASTER GOALS TABLE?

I just need to know if this CAN BE DONE?  The relationship between the GOALS table, and the BIG table, would be based on more than one item.  I've never done a relationship on more than one item.  I'M ASSUMING THIS CAN BE DONE, CORRECT?  (This is QUESTION 2).   If so, it would be based on "metric", "location", "month", and "goal."  This would ensure the correct row (location, metric, month, and goal) in the BIG table is updated.

Assuming this is possible, my first step would be to go into the GOALS table, and create the relationship to the big table.  But it needs to be based on 4 items, as mentioned:  metric code, location, month, and goal.

I noticed in SQL Server that, when you first create a relationship, it asks if you want to "initially" update the records in the foreign table.  The answer is YES.  We want the goals in the BIG table to be updated immediately, after creation of the relationship.  Then, have all future goal updates, done automatically whenever someone changes a goal in the goals table.

IS THIS POSSIBLE?  If so, then I just need a few tips on how to ensure the relationship is created initially, so this process works.  Thanks very much for your help.

Gary

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices