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

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

Dropping FK Constraints in a Stored Procedure

I just looked at a coworker's stored procedure and this person is
dropping 4 Foreign key constraints and then re-adding them after
processing the required logic (updating rows in the 4 tables in
question).

Is there *ANY* good reason to do this? What are the performance
implications of doing this - negative or otherwise?

I was furious when I found this because every once in a while I would
notice that the constraints would be in flux...some days they were
there, othere days there were not. I mean, this is a good enough reason
to NOT do this, but I need some additional feedback. Maybe there *is* a
good reason, and that the logic just needs tweaking.

Thanks.The only justification for this technique is developer productivity. It's a
lot easier to write code without those nasty foreign key constraints getting
in the way :-)

Seriously, I can't think of a good reason to do this. There may be some
situations where temporarily removing constraints can improve batch
performance but this kind of thing should only be done during maintenance
windows. Adding and removing constraints during normal operation can lead
to blocking and data integrity issues.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dmitri" <nienna.gaia@.gmail.com> wrote in message
news:1111719489.616490.262060@.f14g2000cwb.googlegr oups.com...
>I just looked at a coworker's stored procedure and this person is
> dropping 4 Foreign key constraints and then re-adding them after
> processing the required logic (updating rows in the 4 tables in
> question).
> Is there *ANY* good reason to do this? What are the performance
> implications of doing this - negative or otherwise?
> I was furious when I found this because every once in a while I would
> notice that the constraints would be in flux...some days they were
> there, othere days there were not. I mean, this is a good enough reason
> to NOT do this, but I need some additional feedback. Maybe there *is* a
> good reason, and that the logic just needs tweaking.
> Thanks.|||Dmitri (nienna.gaia@.gmail.com) writes:
> I just looked at a coworker's stored procedure and this person is
> dropping 4 Foreign key constraints and then re-adding them after
> processing the required logic (updating rows in the 4 tables in
> question).
> Is there *ANY* good reason to do this? What are the performance
> implications of doing this - negative or otherwise?

Good reason...

There is a problem with constraints and triggers in SQL Server, as they
always fire at statemet time. Other products have commit-time constraints
and/or triggers. This is good, because that permits you to violate
constraints temporarily in a transaction where it does not matter. One
example is that you have Orders and OrderDetails, and for some reason
need to reallocating order numbers and the constraints are not defined
as cascading. (Maybe because of the many restrictions with cascading
DRI in SQL Server.)

That said, I would say that it vert bad practice to do this in application
code. For a maintenance procedure or a fix proecedure that is run once in
a blue moon it could be acceptable. If there is some really difficult
situation where you must to this, you *must* do it within the realm of a
transaction, so that the final result of the operation in case of an error
or a power failure is that the constraints are gone.

Performance implications? You bet. If, as you say, he drops and re-adds
the constraints, there is a cost for checking the constraints. An
alternative would be to disable the constraints and enabling them
again without checking. In this case, the constraints would not be
trusted by the optimizer, and this could affect query plans. (And of
course, if his logic violated the constraints, no one would know.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Standard SQL has a DEFERABLE option on constraints. They can be turned
on or off by default or by action. But all constraints have to be true
at COMMIT time. I would guess that he is used to DB2 or another SQL
that hs this feature.

SQL Server does not work this way. So this is very dangerous and he
should not be doing it. He can lock up or trash the whole system with
his stored procedures. My guess would be that his procedure ought to
update the table in the proper order, or that the schema has something
really ugly in it, like a cyclic reference.

If this has been goign on for awhile, you better do a data audit, too.|||Thanks for the input all!

Dropping Clustered index associated with Primary Key.

Hi all,

I have a huge table with million of rows, which has ONE Clustered index associated with the PRIMARY KEY, and there are some NON_Clustered indexes.

So,now i decided that, i dont need any more indexes ( not even one) on that table, but i need to maintain primary key on that table.

(a) So, how can i accomplish this (i.e.) having primay key but not having indexes on the table.

Thanks.

From BOL.

"When you specify a PRIMARY KEY constraint for a table, the SQL Server 2005 Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes."

so basically you can't have a primary key without any indexes. Although you can have PK with a non-clustered index.

I am curious what made you to go down this path to remove the indexes on this table. What are you trying to achieve here?

|||

Actually, there is a big file, which bulk inserts into that huge table,and that table is very rarely used for selects statements.

So, i was thinking , if we could remove the indexes before bulk inserting the data and then rebuild them ( as we build , every week), we can improve the performance of bulk inserting the data into that table.

If you/anyone have any other idea about dealing with this kind of scenario, plz let me know.

Your help is greatly appreciatied.

Thanks.

|||In this scenario, whether to drop the indexes and re-create them after bulk insert or bulk insert data without dropping indexes depends on the ratio of data in the table vs the data coming to the table. If there are already millions of rows, and lot more millions to come then I think its best to drop the indexes and recreate them. Also you can pick up some performance, if you can split the one big file into smaller files and importing them concurrently by specifying TABLOCK after dropping the indexes. Make sure 'select into/bulkcopy' option is set to true.
|||

Along with the other suggestions, also consider changing the recovery model of the database to BULK LOGGED or SIMPLE recovery.

HTH!

|||

Thanks sankar and rich for your suggestions.

Actually there are 9 Non-Clustered indexes and a Clustered index associated with the Primary key on that huge table.

I can drop the non-clustered indexes, But, the Clustered index is associated with Primary key. How can i drop the clustered index, by having primary key on the table.

If i drop the Primary key consraint , then clustered index will be removed. But inserting data on a table without primary key , can lead into data inconsistency ( i mean, some duplicates/null can come into the table).

Thanks for your help.

|||

As Sankar mentioned, if you want a primary key or indeed any constraint to enforce uniqueness you will have an index. This is not something you can change.

You are right in saying that having it protects the integrity of your data, so unless you are able to drop the primary key, insert the data, tidy up any dupes and then reapply the primary key you're going to have to live with the fact that you have an index.

Dropping an old foreign key restraint?

This forum looked like the only one where I may get an answer to this question.

I built a database with a set of tables in Sql Server 2005 sp2.

I later had to refactor the data which led to removing one table, renaming another table, and moving some data around.

Now when I attempt to write my test code and execute my initialization logic (which clears data out of the tables) I get a foreign key restraint error when trying to delete one of my records. However the FK restraint is an old one that refers to a table that no longer exists. I have looked at all of my tables in the current design and the constraint referenced in the exception does not show up on any of the tables.

How things changed: I had DesktopItems tie to a DataEntry table with a foreign key constraint. I now have DesktopItems point to WorkItem ( the orgininal table renamed ) and it is on the delete of items from DesktopItems that I get the old FK constraint. The really odd part about this is if I go into the table and manually delete the record, I do NOT get the constraint error!!!

In the database that I'm getting the error, I implemented the changes by deleting all tables and SPROCS and then executing scripts to rebuild the new design.

Is there a system SPROC that I can execute to show and delete this item?

Dear Michael,

Try this code:

IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'[FK_DesktopItems_TO_DataEntry]'))
IF OBJECT_ID (N'[DesktopItems]') IS NOT NULL
EXEC ('ALTER TABLE [DesktopItems] DROP CONSTRAINT FK_DesktopItems_TO_DataEntry')

HTH,

Suprotim Agarwal

Dropping an old foreign key restraint?

This forum looked like the only one where I may get an answer to this question.

I built a database with a set of tables in Sql Server 2005 sp2.

I later had to refactor the data which led to removing one table, renaming another table, and moving some data around.

Now when I attempt to write my test code and execute my initialization logic (which clears data out of the tables) I get a foreign key restraint error when trying to delete one of my records. However the FK restraint is an old one that refers to a table that no longer exists. I have looked at all of my tables in the current design and the constraint referenced in the exception does not show up on any of the tables.

How things changed: I had DesktopItems tie to a DataEntry table with a foreign key constraint. I now have DesktopItems point to WorkItem ( the orgininal table renamed ) and it is on the delete of items from DesktopItems that I get the old FK constraint. The really odd part about this is if I go into the table and manually delete the record, I do NOT get the constraint error!!!

In the database that I'm getting the error, I implemented the changes by deleting all tables and SPROCS and then executing scripts to rebuild the new design.

Is there a system SPROC that I can execute to show and delete this item?

Dear Michael,

Try this code:

IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'[FK_DesktopItems_TO_DataEntry]'))
IF OBJECT_ID (N'[DesktopItems]') IS NOT NULL
EXEC ('ALTER TABLE [DesktopItems] DROP CONSTRAINT FK_DesktopItems_TO_DataEntry')

HTH,

Suprotim Agarwal

Dropping an auto numbered primary key and add a new one

The table I am using have a column called Key which is the primary key of the table and a auto number. This primary key is not a foreign key in any other table.

I need to write SQL to drop the current primary key and add a new one Say "RecordId"

as the new primary key and which should be a autonumber too.

any idea.

thanks in advance

droping and adding a primary key on a column wont affest its 'identity' property, so simply drop the primary key and add again with the new name u want...

alter table tablename drop constraint oldpk

alter table tablename add constraint newpk primary key(id)

|||

What's the point? It is easy enough, just drop the column and add another one, but there might be an easier way to do what you are wanting to do.

Here is a script that does it:

set nocount on
drop table test
go
create table test
(
testId int identity constraint PKtest primary key,
value datetime default (getdate())
)
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
go
select *
from test
go
alter table test
drop PKtest
go
alter table test
drop column testId
go
select *
from test
go
alter table test
add testId int identity (2,2) constraint PKtest primary key
go
select *
from test --the identity values will start at 2 and go up by steps of 2 (so you can see the diff)

sql

Drop table's primary key with knowing the constraint's name

Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name =
(SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Drop table's primary key with knowing the constraint's name

Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name = (SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FK
Joe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
David Portas
SQL Server MVP

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FKJoe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
--
David Portas
SQL Server MVP
--

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FKJoe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below
.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
David Portas
SQL Server MVP
--

Drop Primary key constraint of (#) Hash

Hi there,

I have created a hash table. After using it, somehow the primary key
constraint of this hash table still exist in database. Which cause
error.

When I delete this constraint with Alter table Drop con...
It gives no table exist error.

Can anybody give any idea.

Thanks in Adv.,
T.S.Negi> I have created a hash table. After using it, somehow the primary key
> constraint of this hash table still exist in database. Which cause
> error.

I assume that by "hash table" you mean a temporary table? What makes you
think that the PK exists after the table is dropped? (Don't trust the output
of Enterprise Manager as that isn't always refreshed when you would expect
it to be) What error is caused and what is the code that produces the error?

--
David Portas
SQL Server MVP
--|||tilak.negi@.mind-infotech.com (T.S.Negi) wrote in message news:<a1930058.0408102110.40ec8809@.posting.google.com>...
> Hi there,
> I have created a hash table. After using it, somehow the primary key
> constraint of this hash table still exist in database. Which cause
> error.
> When I delete this constraint with Alter table Drop con...
> It gives no table exist error.
>
> Can anybody give any idea.
> Thanks in Adv.,
> T.S.Negi

I don't quite understand your issue - this works fine for me:

create table #t (col1 int not null)
alter table #t add constraint PK_t primary key (col1)
alter table #t drop constraint PK_t
drop table #t

It would be best to post your version of MSSQL, the exact error
messages you get, as well as some explanation of why you believe the
constraint exists but the table doesn't.

Simon

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. Is
there a way to drop the clustered index without creating new indexes? Or have
the new indexes ignore dulicates?
Thanks
Have you got a complete repro? I'm wondering if there is a foreign key from
another table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks
|||My guess is that you have a corruption problem. Seem you have a unique nc index in which you have
managed to get duplicates. So the dropping of the clustered index will re-create the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem. Is
> there a way to drop the clustered index without creating new indexes? Or have
> the new indexes ignore dulicates?
> Thanks

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. I
s
there a way to drop the clustered index without creating new indexes? Or hav
e
the new indexes ignore dulicates?
ThanksHave you got a complete repro? I'm wondering if there is a foreign key from
another table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks|||My guess is that you have a corruption problem. Seem you have a unique nc in
dex in which you have
managed to get duplicates. So the dropping of the clustered index will re-cr
eate the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem.
Is
> there a way to drop the clustered index without creating new indexes? Or h
ave
> the new indexes ignore dulicates?
> Thanks

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. Is
there a way to drop the clustered index without creating new indexes? Or have
the new indexes ignore dulicates?
ThanksHave you got a complete repro? I'm wondering if there is a foreign key from
another table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks|||My guess is that you have a corruption problem. Seem you have a unique nc index in which you have
managed to get duplicates. So the dropping of the clustered index will re-create the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem. Is
> there a way to drop the clustered index without creating new indexes? Or have
> the new indexes ignore dulicates?
> Thanks

Drop an unnamed primary key....

Hello!
How can I drop an unnamed primary key with a query?
Else how can I recover its name assigned by SQLServer?
Thanks for your help> Else how can I recover its name assigned by SQLServer?
http://www.aspfaq.com/2104|||This is exact but if I'm connected to database as an user 'alpha' (which
is db_owner),
the clause 'c_obj.uid = user_id()' in query view prevents me from
recovering the query data
And I can't modify the query view on database.....
"Aaron Bertrand [SQL Server MVP]" a crit :

> http://www.aspfaq.com/2104|||Frdric Armani wrote:
> Hello!
> How can I drop an unnamed primary key with a query?
> Else how can I recover its name assigned by SQLServer?
> Thanks for your help
Just run sp_help on the table and look in the index and/or contraint
sections for the auto-generated name
create table dbo.Whatever123 (
col1 int not null primary key,
col2 int )
go
sp_help Whatever123
go
constraint_type constraint_name
-- --
PRIMARY KEY (clustered) PK__Whatever123__591CDF8E
drop table dbo.Whatever123
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> the clause 'c_obj.uid = user_id()' in query view
What clause in what query view? Did you run:
SELECT
T.TABLE_NAME,
T.CONSTRAINT_NAME,
K.COLUMN_NAME,
K.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE
T.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND T.TABLE_NAME = 'table_name'|||Hi Frederic,
To find the PK name, use this:
SELECT name FROM sysobjects
WHERE parent_obj=OBJECT_ID('YourTable') AND xtype='PK'
To drop the PK, use this (if you don't mind using an undocumented SP):
sp_execresultset 'SELECT ''ALTER TABLE ''
+QUOTENAME(OBJECT_NAME(parent_obj))
+'' DROP CONSTRAINT ''+QUOTENAME(name)
FROM sysobjects WHERE parent_obj=OBJECT_ID(''YourTable'')
AND xtype=''PK'''
Of course, replace YourTable with your table name.
Razvan

Drop a constrain

Hi,
I need to delete a table WITH foreign key constrain. What is the fast way to
drop the table?
ThanksHi all,
The table has SP and tables as dependencies
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23N7ZVEjjGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I need to delete a table WITH foreign key constrain. What is the fast way
> to drop the table?
> Thanks
>|||On Mon, 12 Jun 2006 11:01:26 -0400, mecn wrote:
>Hi,
>I need to delete a table WITH foreign key constrain. What is the fast way to
>drop the table?
ALTER TABLE MyTable
DROP CONSTRAINT MyConstraint;
DROP TABLE MyTable;
--
Hugo Kornelis, SQL Server MVP