Error: Msg 3723, It is being used for PRIMARY KEY constraint enforcement.

When we execute following query to sql management query windows:

DROP INDEX tblEmployees.PK__Table__3214EC079D3D6FE5

When you execute it we will get following msg 3723

will get:

--Msg 3723, Level 16, State 4, Line 1

--An explicit DROP INDEX is not allowed on index 'Table.PK__Table__3214EC079D3D6FE5'.

--It is being used for PRIMARY KEY constraint enforcement.

This error message will get when you are going to drop a clustered index on table using query window.

So it stated that you need to drop primary key constraint of the table. And we know that table has only one clustered index so you can create only one clustered index in the table but you can add more columns to the clustered index.

How to solve this error:

You can write following query to the query window and select

ALTER TABLE [Table]
DROP CONSTRAINT PK__Table__3214EC079D3D6FE5

Now if you will refresh the index folder of table you should see the primary key clustered index removed from it.

You can also remove using graphically for that one you need to expand the table and go to table and expand index folder where you can see primary key clustered index with PK_ prefix right click on it and press on delete. It will delete pk index.

Learn about SQL with simple syntax

What?

SQL – Structured Query Language

Definition: Storing, Manipulating and retrieving data stored in relational database.

Why?

• Allows us to create a new database, tables
• Allows us to create view, stored procedure, function in the database.
• Allows user to describe data.
• Allows user to access data in relational database management which is call as RDBMS.
• Allows user to set permissions.

How?

The standard SQL commands to interact with Relational Database Management System are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP.

These commands are classified in the following groups.

DDL – Data Definition Language

     1.      CREATE

a.     Create a new table, a view or other object in database.

b.     Syntax:

Go

USE TestExample //Name of the Database

Go

CREATE TABLE [dbo].[Blog]

(

 BlogId int IDENTITY(1,1),

 Name varchar(250),

 Url varchar(max)

);

      2.      ALTER

a.     Edit or modified an existing database object, example table.

b.     Syntax:

GO

USE TestExample

GO

ALTER table [dbo].[Blog]

ALTER column Url varchar(250)

      3.      DROP

a.     Delete or remove entire table, a view or other object in the database.

b.     Syntax:

GO

USE TestExample

GO

ALTER TABLE [dbo].[Blog]

DROP COLUMN Url

DML – DATA MANIPULATION LANGUAGE

     1.      SELECT

a.     Retrieve records of one or more tables.

b.     Syntax:

Select * from [dbo].[Blog];

Select BlogId as "ID",Name as "Name" from [dbo].[Blog];

     2.      INSERT

a.     Add or Insert a new record to the database table.

b.     Syntax:

Insert into [dbo].[Blog] values('Ado.Net Blog');

Insert into [dbo].[Blog](Name) values('Entity Framework');

     3.      UPDATE

a.     Change or Update a record to the database table.

b.     Syntax:

Update [dbo].[Blog] set Name='Entity Framework' where BlogId = 2;

     4.      DELETE

a.     Remove or Delete records to the database table.

b.     Syntax:

Delete from [dbo].[Blog] where BlogId = 3; // Delete single record.

Delete from [dbo].[Blog]; // Delete entire records of the table.

DCL – DATA CONTROL LANGUAGE

     1.      GRANT

a.     Give us to privilege to user.

b.     You can grant on various database object in SQL server. Like Select, Insert, Update, Delete, References, Alter and All. Here object may be Database or Tables and the name of the user that will be granted these privileges.

c.     Syntax:

GRANT privileges ON object TO user;

     2.      REVOKE

a.     Takes back privilege granted from user.

b.     Once you have granted privileges to the user. You may need to revoke some or all of these privileges. For that you can revoke any combination of Select, Insert, Update, Delete, References, Alter or All.

GRANT SELECT ON TestExample To public;

GRANT SELECT, INSERT, UPDATE, DELETE ON TestExample TO sa;

drop a column or record with default value constraint in sql server

If we want to drop particular field/column of the table then using Alter Table name of the table and Drop Column name of the column so we can do it using that syntax.

But if want to drop a field/column that has default constraint then it is very hard to do without its perfect syntax.

To drop default constraint column value you should familiar with default constraint name after then you can execute a simple Alter table name of table drop constraint name of the constraint.

Syntax:

Step 1

DECLARE @ConstraintName nvarchar(200)

SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'__ColumnName__' AND object_id = OBJECT_ID(N'__TableName__'))

IF @ConstraintName IS NOT NULL

EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

IF EXISTS (SELECT * FROM syscolumns WHERE id=object_id('__TableName__') AND name='__ColumnName__')

EXEC('ALTER TABLE __TableName__ DROP COLUMN __ColumnName__')

Step 2

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__')

BEGIN

SELECT @ConstraintName = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__'

EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

END

I will explain how to drop column with default constraint column value with my simple understanding methodology if you do not want to use above syntax.

Step 1: Get column id of your default constraints which you want to drop. Using following syntax we can get it. Name = N’_ column name _’ and OBJECT_ID = ‘_ Table Name _’ using object_id() built in function.

SELECT column_id FROM SYS.COLUMNS WHERE NAME = N'URL' AND OBJECT_ID = OBJECT_ID('[dbo].[TestExample11]');

Step 2: After getting column id give it to below Sys.Default_Constraints so you will get Name of Default Constraint of that column.

SELECT NAME FROM SYS.DEFAULT_CONSTRAINTS  WHERE PARENT_COLUMN_ID = 3 AND PARENT_OBJECT_ID = OBJECT_ID('[dbo].[TestExample11]');

Step 3: Use following syntax finally to remove default constraints.

ALTER TABLE [dbo].[TestExample11]

DROP CONSTRAINT DF__TestExample__URL__1273C1CD;

I hope you understand this well now. Also you can change this syntax like below.