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
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Thursday, March 29, 2012
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
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
Tuesday, March 27, 2012
dropping datafile from primary filegroup
Is there a way to drop a datafile from the primary
filegroup after running the dbcc shrinkfile
(file_name,emptyfile).
There is a lot of fragmentation in the datafile and the
file is not shrinking. The size of the datafile right now
is 15 gig and no more than 5gig of data is present in the
datafile . After running the above dbcc command the
contents of the file will be moved onto the remaining data
files in the filegroup and allows the file to be dropped .
But this is not he case with the primary
filegroup/datafile and it errors out. Is there any other
way to drop the datafile or shrink the file to reduce the
fragmentationYou can't drop the Primary file or filegroup. But you can run DBCC
DBREINDEX or DBCC INDEXDEFRAG to reduce the fragmentation.
Andrew J. Kelly
SQL Server MVP
"rajeev" <potinenir@.yahoo.com> wrote in message
news:058201c37d6d$22e20190$a401280a@.phx.gbl...
> Is there a way to drop a datafile from the primary
> filegroup after running the dbcc shrinkfile
> (file_name,emptyfile).
> There is a lot of fragmentation in the datafile and the
> file is not shrinking. The size of the datafile right now
> is 15 gig and no more than 5gig of data is present in the
> datafile . After running the above dbcc command the
> contents of the file will be moved onto the remaining data
> files in the filegroup and allows the file to be dropped .
> But this is not he case with the primary
> filegroup/datafile and it errors out. Is there any other
> way to drop the datafile or shrink the file to reduce the
> fragmentation
>sql
filegroup after running the dbcc shrinkfile
(file_name,emptyfile).
There is a lot of fragmentation in the datafile and the
file is not shrinking. The size of the datafile right now
is 15 gig and no more than 5gig of data is present in the
datafile . After running the above dbcc command the
contents of the file will be moved onto the remaining data
files in the filegroup and allows the file to be dropped .
But this is not he case with the primary
filegroup/datafile and it errors out. Is there any other
way to drop the datafile or shrink the file to reduce the
fragmentationYou can't drop the Primary file or filegroup. But you can run DBCC
DBREINDEX or DBCC INDEXDEFRAG to reduce the fragmentation.
Andrew J. Kelly
SQL Server MVP
"rajeev" <potinenir@.yahoo.com> wrote in message
news:058201c37d6d$22e20190$a401280a@.phx.gbl...
> Is there a way to drop a datafile from the primary
> filegroup after running the dbcc shrinkfile
> (file_name,emptyfile).
> There is a lot of fragmentation in the datafile and the
> file is not shrinking. The size of the datafile right now
> is 15 gig and no more than 5gig of data is present in the
> datafile . After running the above dbcc command the
> contents of the file will be moved onto the remaining data
> files in the filegroup and allows the file to be dropped .
> But this is not he case with the primary
> filegroup/datafile and it errors out. Is there any other
> way to drop the datafile or shrink the file to reduce the
> fragmentation
>sql
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 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)
Wednesday, March 21, 2012
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
--
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
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
Monday, March 19, 2012
Drop Primary Transaction Log File
Hi All,
I attemping to move a transaction log file online to another location. I performed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file cannot be removed from a database".
Is it possible to remove the original log file?
Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/de...us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
I attemping to move a transaction log file online to another location. I performed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file cannot be removed from a database".
Is it possible to remove the original log file?
Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/de...us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
Drop Primary Transaction Log File
Hi All,
I attemping to move a transaction log file online to another location. I per
formed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file
cannot be removed from a database".
Is it possible to remove the original log file?Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/d...-us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
I attemping to move a transaction log file online to another location. I per
formed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file
cannot be removed from a database".
Is it possible to remove the original log file?Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/d...-us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
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
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
--
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
--
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
Friday, March 9, 2012
Drop and Recreate subscription
I need to drop and recreate few subscriptions in transactional publication
Do I need to worry about log marker issues ?
Do I need to set the primary and replicate databases in 'DBO use only'
The Primary and Replicate databases are being accessed all the time.To your two questions, the answers are
No and No.
Do I need to worry about log marker issues ?
Do I need to set the primary and replicate databases in 'DBO use only'
The Primary and Replicate databases are being accessed all the time.To your two questions, the answers are
No and No.
Wednesday, March 7, 2012
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
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
Subscribe to:
Comments (Atom)