Dropping Primary Key once a week

The company I work for is planning to use a new software package for its
internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
histories, etc.
The software uses the prmary key to contain the customer identification #.
Our business needs requires us to change the customer identification # for
certain customers. This would mean changing the value of the primary key.
The consultants are planning to have a routine that drops all of the indexes
and constraints, change the values then rebuild all of the tables.
This seems like a dangerous idea to me however it is not my decision. I was
hoping to get opinions or links to documentation warning against this - or -
is there nothing to worry about?
Any documentation, details, or advice would be appreciated. If there is a
more appropriate forum for this type of question, please let me know.
Thanks in advance
Dave
Just what is this "customer identification number" and where does it come
from? What's the reason for making the changes?
A well-chosen key should change seldom and I'm sure there must be a better
way to meet your requirements than what you have described. It sounds like
you have a fundamental data-modelling problem.
David Portas
SQL Server MVP
|||In addition to David's comments, I would consider getting some new
consultants for a number of reasons. First, changing the schema of a
packaged product is (or should be considered) equivalent to changing the
code; this is an action not likely supported by your license. Second,
dropping the PK means that you would need to disable the "system" for the
duration of this activity; is this an acceptable business limitation?
Lastly, there are likely to be other alternatives (such as some type of
"translation" mechanism that can be done outside of the product's packaged
schema). Your consultants should be identifying all possible alternatives
for accomplishing the business goal / process, evaluating the pros/cons, and
justifying their adopted approach. If not, then you need new consultants.
If your company is not requiring this, then your company very much needs
real help that it is not getting from the consultants. This might be your
lucky break! Good luck.
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> The company I work for is planning to use a new software package for its
> internal use. SQL 2000 -Approx. 100,00 customer records linked to orders,
> histories, etc.
> The software uses the prmary key to contain the customer identification #.
> Our business needs requires us to change the customer identification # for
> certain customers. This would mean changing the value of the primary key.
> The consultants are planning to have a routine that drops all of the
indexes
> and constraints, change the values then rebuild all of the tables.
> This seems like a dangerous idea to me however it is not my decision. I
was
> hoping to get opinions or links to documentation warning against this -
or -
> is there nothing to worry about?
> Any documentation, details, or advice would be appreciated. If there is a
> more appropriate forum for this type of question, please let me know.
> Thanks in advance
> Dave
>
|||Hi Dave
The customer identification number is the number our customer uses in their
daily business. It is assigned to our customers based on their company name.
If the company name is changed, so is the #. Its confusing, but unfortunately
it is not possible to change this requirement.
I agree with the fundamental data problem. What I'm hoping for is concrete
examples & documentation on the risks caused by dropping and recreating the
primary key after changing the values in some rows. It may be possible to
persuade upper management from moving ahead with the consultants suggestion.
Thanks
Dave
"David Portas" wrote:

> Just what is this "customer identification number" and where does it come
> from? What's the reason for making the changes?
> A well-chosen key should change seldom and I'm sure there must be a better
> way to meet your requirements than what you have described. It sounds like
> you have a fundamental data-modelling problem.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> The customer identification number is the number our customer uses in
their
> daily business. It is assigned to our customers based on their company
name.
> If the company name is changed, so is the #. Its confusing, but
unfortunately
> it is not possible to change this requirement.
There is no problem with this, as long as you don't use this ID as a primary
key. Use a surrogate key, and this problem disappears.
|||Thanks Scott, I agree with your comments. I'm hoping to educate myself on all
of the potential dangers of the consultants plans to be more effective in
supporting an alternative. Any details, documents, comments are very
appreciated.
The consultants believe that if the changes are done overnight - the
timeframe would not be a problem to have everything functioning the next day.
Please keep sharing your thoughts - I am appreciative of your comments.
"Scott Morris" wrote:

> In addition to David's comments, I would consider getting some new
> consultants for a number of reasons. First, changing the schema of a
> packaged product is (or should be considered) equivalent to changing the
> code; this is an action not likely supported by your license. Second,
> dropping the PK means that you would need to disable the "system" for the
> duration of this activity; is this an acceptable business limitation?
> Lastly, there are likely to be other alternatives (such as some type of
> "translation" mechanism that can be done outside of the product's packaged
> schema). Your consultants should be identifying all possible alternatives
> for accomplishing the business goal / process, evaluating the pros/cons, and
> justifying their adopted approach. If not, then you need new consultants.
> If your company is not requiring this, then your company very much needs
> real help that it is not getting from the consultants. This might be your
> lucky break! Good luck.
> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:301C31A4-9ECD-4FE5-80E6-B494D9BAB180@.microsoft.com...
> indexes
> was
> or -
>
>
|||Assuming you can take the system offline and assuming the changes themselves
won't violate data integrity inside or outside your system, I don't see any
particular risks - just a lot of disruption and associated costs. If your
system is that poorly suited to your business needs then I would say your
consultants' time would be better spent looking for a new system.
David Portas
SQL Server MVP
|||Mark - thats the problem - they want to use the primary key. They plan to
drop all indexes, triggers, constraints, etc...change the data then rebuild
- once a week.
What are the potential problems?
Thanks
Dave
"Mark Wilden" wrote:

> "ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
> news:3C48D110-4FC5-4F4C-9F1B-B0415CBCE47E@.microsoft.com...
> their
> name.
> unfortunately
> There is no problem with this, as long as you don't use this ID as a primary
> key. Use a surrogate key, and this problem disappears.
>
>
|||One of the risks is that the recreation process will either fail or will
rebuild the schema with something other than the most current DDL. Over
time, it is inevitable that the schema will change (either by your company,
MS, or your system vendor). The rebuild process must be kept current with
the schema - a responsibility that falls on your company.
If the rebuild process fails, what is the recovery strategy?
What timeframe is required for this entire process? Any estimates from the
consultants? This assumes that someone has generated an estimate for the
maximum size of the database (which will affect the rebuild speed).
Has anyone discussed this strategy with your system vendor (assuming you
have a support agreement of some type)?
While the proposed process is do-able, it sounds like your organization does
not have a lot of experience with RDBMS servers. If true, this lack of
experience is a risk. Will the consultants be responsible for the rebuild
process? If not, will they be available (and how quickly) if "something"
happens? Does your organization have a support structure that can handle a
failure with assistance?
"ddeegan" <ddeegan@.discussions.microsoft.com> wrote in message
news:CDB9B3AF-FC42-4875-96FE-4E5D5A870124@.microsoft.com...
> Mark - thats the problem - they want to use the primary key. They plan to
> drop all indexes, triggers, constraints, etc...change the data then
rebuild[vbcol=seagreen]
> - once a week.
> What are the potential problems?
> Thanks
> Dave
> "Mark Wilden" wrote:
primary[vbcol=seagreen]
|||On Fri, 3 Dec 2004 11:11:05 -0800, ddeegan wrote:
(snip)
>Our business needs requires us to change the customer identification # for
>certain customers. This would mean changing the value of the primary key.
>The consultants are planning to have a routine that drops all of the indexes
>and constraints, change the values then rebuild all of the tables.
(snip)
Hi Dave,
What is the reason for dropping and recreating the indexes and
constraints? What is the reason for not using cascading updates?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql