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
DaveJust 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...
> > 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
> >
>
>|||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...
> >
> > 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.
>
>|||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
> - 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...
> > >
> > > 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.
> >
> >
> >|||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)|||On Fri, 3 Dec 2004 11:11:05 -0800, "ddeegan"
<ddeegan@.discussions.microsoft.com> wrote:
>Any documentation, details, or advice would be appreciated. If there is a
>more appropriate forum for this type of question, please let me know.
I concur with the other comments already posted.
This sounds like a major goofy idea. Disable constraints and keys to
change a handful of data values? These guys must be nuts. What harm
is there? No HARM. Maybe they should translate all the names to
Chinese and back every night, too. What's the harm in that? Maybe
you want to rebuild your car's engine every night. What's the harm in
that? Might be a good thing, right? Yeah, right.
Use surrogate keys if this is a serious problem, cascades if you must,
or write some SP's to do it safely. Lots of better alternatives that
requires zero (0) downtime.
J.|||As the others say, the total rebuild of 'everything' is probably a bad idee.
(What is this goes wrong ?).
As the others say, can't the design be 'mended' so the problem does not
occure.
For the following answers I am going to suppose the a redesign is not
feasable within your situation. And that customer names still change and
that they are part of primairy key's and relations.
Solutions could be :
1.
Cascading.
But then all relations have to be present and in the correct 'Order', if the
relations
are not like a tree, cascading might not be possible. (For example if there
are double parallel relations, or self referencing tables).
2.
Switch of the constraints of the concerned tables.
Alter the customer id's.
Switch on the constraints of the concerned tables.
3.
For all tables which are 'parent' tables to other tables. Make a new entry
(duplicate) with
the 'new' customer name.
Update all the children of that customer.
Then delete all rows which have been duplicated.
This method has to be implemented very carefully, the order of tables has to
be correct.
There should be no unique constraint (not involving the customer_id) in the
tables.
Duplication has to be done top down. Deletion has to be done bottom up.
4.
Take all information of a customer who changes it's name out of the database
and
reenter it with the correct customer_id.
(Offcourse this has to be done in the correct order for the tables).
In all for of the above method's most changes have (or at least is prevered)
to be done
on one customer at the time. (Then the load and impact can be spread over a
larger
period so that normal performance is less hindered or interrupted).
The number of customer changing their names can not be that huge I suppose.
Method 2 is probably the easiest to implement, but switching of a constraint
of a table,
gives a large resource lock on that table. But then again the change of one
customer
can probably be done in a few seconds.
Good luck,
Please keep us informed you how you are going to tackle your problem.
And how it works when implemented.
Greetings,
Ben Brugman
"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
>|||Hello - just wanted to say thanks for all the helpful responses. I documented
everything and forwarded it up the ladder.
Alot of contracts have been signed, so we have to move ahead the best we can
and keep working towards a solution.
The comparison to rebuilding the car engine was great.
Thanks
Dave
"ben brugman" wrote:
> As the others say, the total rebuild of 'everything' is probably a bad idee.
> (What is this goes wrong ?).
> As the others say, can't the design be 'mended' so the problem does not
> occure.
> For the following answers I am going to suppose the a redesign is not
> feasable within your situation. And that customer names still change and
> that they are part of primairy key's and relations.
> Solutions could be :
> 1.
> Cascading.
> But then all relations have to be present and in the correct 'Order', if the
> relations
> are not like a tree, cascading might not be possible. (For example if there
> are double parallel relations, or self referencing tables).
> 2.
> Switch of the constraints of the concerned tables.
> Alter the customer id's.
> Switch on the constraints of the concerned tables.
> 3.
> For all tables which are 'parent' tables to other tables. Make a new entry
> (duplicate) with
> the 'new' customer name.
> Update all the children of that customer.
> Then delete all rows which have been duplicated.
> This method has to be implemented very carefully, the order of tables has to
> be correct.
> There should be no unique constraint (not involving the customer_id) in the
> tables.
> Duplication has to be done top down. Deletion has to be done bottom up.
> 4.
> Take all information of a customer who changes it's name out of the database
> and
> reenter it with the correct customer_id.
> (Offcourse this has to be done in the correct order for the tables).
> In all for of the above method's most changes have (or at least is prevered)
> to be done
> on one customer at the time. (Then the load and impact can be spread over a
> larger
> period so that normal performance is less hindered or interrupted).
> The number of customer changing their names can not be that huge I suppose.
> Method 2 is probably the easiest to implement, but switching of a constraint
> of a table,
> gives a large resource lock on that table. But then again the change of one
> customer
> can probably be done in a few seconds.
> Good luck,
> Please keep us informed you how you are going to tackle your problem.
> And how it works when implemented.
> Greetings,
> Ben Brugman
>
> "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
> >
>
>|||First and foremost, the definition of a database is a physical represention
of the logical relationships between data. The primary reason for the
creation of the Relational Theory of Data was to put into a mathematical
framework those ideas essential for the manipulation, integrety, and
consistency of data models.
Without data integrity, you do not have a database nor the garauntee of
logical consistency.
The modification of parent information is well formulated with the
requirement that all parent data is replicated with the updated information
and then all dependent child data is migrated to this new information, only
then is the original parent data removed. All the while, leaving the
Declaritive Referential Integrity constraints online. If they are not,
there is no garauntee of data consistency and, thus, no garuantee of a
consistent database.
Now, I said DRI, this does not include procedural referential integrity:
triggers, stored procedures, and/or API processes. All of these can be
disabled with simple statements without having to drop and recreate them,
nor should the compromise the database consistency or integrity if we are
only relabeling currently consistent data. However, as others have pointed
out, the system would need to be offline while these operations occurred.
As far as documentation and litereature, check out:
E. F. Codd's original works on the Fundemental Theory of Data
A Relational Model of Data for Large Shared Databanks,
http://www.acm.org/classics/nov95/toc.html,
C. J. Date's An Introduction to Database Systems,
http://www.dbdebunk.com/books.html,
and/or Fabian Pascal's Practical Issues in Database Management.
Sincerely,
Anthony Thomas
"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