Error 3723 Sql Server
Contents |
Msg 3723 11 Jul An Explicit DROP INDEX is Not Allowed On the Index, Msg 3723 Posted at 10:38h in MSSQL, SQL MVP, SQLServerPedia Wiki by Thomas LaRock 7 Comments ShareGreat title, huh?Anyway, I came across this error last week so I thought it is being used for unique key constraint enforcement. I would do a quick post in case anyone else comes across this same it is being used for foreign key constraint enforcement issue when trying to drop an index. The error message is very clear for this:--Msg 3723, Level 16, State 4, Line sql server drop unique constraint 1 --An explicit DROP INDEX is not allowed on index 'Table.PK__Table__3214EC277BB05806'. --It is being used for PRIMARY KEY constraint enforcement.OK then, it seems that we need to remove the constraint first, which can be done
Alter Table Drop Constraint
with:ALTER TABLE [Table] DROP CONSTRAINT [PK__Table__3214EC277BB05806] GOAnd the index, along with the primary key (and that error message), will be gone.If you happen to notice the MSDN entry on DROP INDEX, you will see that this behavior is noted. In case you overlooked it, the relevant comment is this:"The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. To remove the constraint and alter table drop index corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause."So, there you go. You'll thank me later.Like this post? Share if you care!TwitterFacebookLinkedInGoogleHacker NewsRedditEmailMoreTumblrPinterest Graeme Martin You ROCK. Thanks. (See? It's later.) Ajay Can we apply the above workaround for SYSUSERINFO Table ??/ ThomasLaRock Um, I suppose you might be able to do so, but the real question I have is why would you need to drop an index against that table? Guest Various reasons…say you need to change the length of a field being used as a primary key - you have to drop the index first, then change the field, then recreate the constraint. If you think about it, there are many reasons one would need to do something like this from time to time. ThomasLaRock Yes, I understand the needs for altering table schemas from time to time.However, what the OP (Ajay) was asking about here is the SYSUSERINFO table, which I believe is a system table for MS Dynamics. That is what led to my comment asking why they would need to make alterations against that table, because I wanted to know more about what they were trying to accomplish with Dynamics.If you can provide a use case for altering this table in Dynamics, let me k
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads drop primary key with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Cannot drop index because of foreign key constraint enforcement up vote 2 down vote favorite I can't drop an index because some tables used it for foreign key Msg 3723, Level 16, State 6, Line 1 An explicit DROP INDEX http://thomaslarock.com/2011/07/an-explicit-drop-index-is-not-allowed-on-the-index-msg-3723/ is not allowed on index 'tbl1.ix_cox'. It is being used for FOREIGN KEY constraint enforcement. I tried to disable the index first before droping ALTER INDEX ix_cox On tbl1 DISABLE Go But still don't able to drop it. Do I really need to remove the foreign key on those tables that used that index? Because it is about 30 tables. sql sql-server-2008 tsql indexing share|improve this question asked Jul 17 '14 at 3:39 Karlx Swanovski 70641636 add a comment| 2 Answers 2 active oldest http://stackoverflow.com/questions/24794521/cannot-drop-index-because-of-foreign-key-constraint-enforcement votes up vote 3 down vote I am afraid this is the only option you have. you have to drop all foreign key constraints referencing to table and also you cannot recreate the foreign key constraints until you specify another unique index on the table from where you removed index. share|improve this answer answered Jul 17 '14 at 5:33 meer 1263 add a comment| up vote 0 down vote You can disable the FK constraint for temporary being -> drop the index -> re-enable the constraint back like ALTER TABLE your_fk_table NOCHECK CONSTRAINT constraint_name drop index ids_name ALTER TABLE your_fk_table CHECK CONSTRAINT constraint_name share|improve this answer answered Jul 17 '14 at 3:43 Rahul 43.4k63462 Same error for me, even with nocheck on the constraint. –Matt Lyons Sep 8 '15 at 2:04 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged sql sql-server-2008 tsql indexing or ask your own question. asked 2 years ago viewed 2555 times active 2 years ago Get the weekly newsletter! In it, you'll get: The week's top questions and answers Important community announcements Questions that need answers see an example newsletter By subscribing, you ag
SERVER - Explanation about Usage of Unique Index and Unique Constraint September 7, 2008Pinal DaveSQL, SQL Performance, SQL Server, SQL Tips and Tricks15 commentsI enjoy reading questions from blog readers and answering them. One of the another SQL enthusiastic is Imran who also regularly answer questions of users on this community blog. Recently he http://blog.sqlauthority.com/2008/09/07/sql-server-explanation-about-usage-of-unique-index-and-unique-constraint/ has answered in detail about when to use Unique Index and when to use Unique Constraint.Cristiano asked following questions :i need to know how work when there is a situation that there is a Unique Key and this field http://calyansql.blogspot.com/2012/05/msg-3723-level-16-state-5-line-1.html “alow null”, but when i am going to create a Unique Key the SQLSERVER saw that there were values duplicated and the values are “nulls”. How do i sove this problem?Imran Mohammed answered in detail :@Cristiano, As you may sql server be aware, Unique Key can allow only one null. Meaning only one null can be accepted not more than that. If you have more than one null on any column, you cannot make that column/field as unique key.I am sure there must be another way of doing this, I would do something like this,1. Since there is no way you can make that field a unique key, how about making a composite key.a) Combine this field with any other it is being unique column/field and make a composite unique key. If any other unique key already exists then add this field to the previous unique key.b) if you do not have any unique key ( I am sure you do), but if you dont, then you can do this always, using Enterprise manager or SSMS object Explorer, create a new Identity column in the table and then create a unique composite key on these two field. ( You can create an identity column if you have data in the table, you DONT have to drop and recreate the table, using EM or SSMS you can always create a identity column in the table).If you consider case a), then make sure there are no duplicates for the combination of new field and the field you chose for unique key.If you create a composite unique key on cola and colb combined then you can have more than one null in cola and in colb, but again the combination of cola and colb should be unique.Once you start making a composite unique key… this concept will be more easier.Try doing this, either in Enterprise Manager or SSMS ( Object Explorer) much easier in interface than executing scripts.Hope this helps. Imran.Reference : Pinal Dave (http://blog.SQLAuthority.com) Tags: Database, DBA, SQL IndexRelated Articles SQL SERVER - Interesting Interview Questions - Difficult SQL Puzzle December 7, 2008Pinal Dave SQLAuthority News - 3 Mil
from a table. An explicit DROP INDEX is not allowed on index 'TestTable.PX_TestTable'. It is being used for UNIQUE KEY constraint enforcement. This cannot be dont using Drop Index, we need to use Alter Index statement to remove index. The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. To remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause." ALTER TABLE [dbo].[TestTable] DROP CONSTRAINT [PX_TestTable] Posted by Kalyan Kumar A No comments: Newer Post Older Post Home Subscribe to: Post Comments (Atom) Welcome This blog is a diary, which helps me as well as others while surfing about SQL Server. Some articles are written by me some were taken as reference from others websites. NewSearch Followers Search This Blog Disclaimer This is my personal blog, the views and opinions are published here are my own. All data and information provided on this site is for informational purposes only. The views and opinions expressed here are of my own and not those of the people or organizations that I may or may not be related with. The code / scripts or any kind of stuff published in this blog are not permitted or certified by my employer or Microsoft SQL Server. While following any advices from blog if you encountered any issues, author is not responsible for that. Blog Archive ► 2016 (3) ► October (1) ► February (2) ► 2015 (8) ► November (1) ► October (2) ► September (1) ► August (1) ► May (1) ► April (2) ► 2014 (44) ► December (1) ► September (2) ► August (1) ► July (2) ► June (6) ► May (6) ► April (6) ► March (5) ► February (5) ► January (10) ► 2013 (95) ► December (2) ► November (5) ► October (12) ► September (21) ► August (13) ► July (8) ► June (4) ► April (8) ► March (10) ► February (5) ► January (7) ▼ 2012 (170) ► December (11) ► November (11) ► October (20)