delete primary and foreign key from the form side

Hi,

As per your requirement you can write Stored Procedure for the Operation, And pass your parameters as well. And use that on your Form. Here I am providing you the solution(Query) .

To DELETE PRIMARY KEY

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = '[table name]' AND TABLE_SCHEMA ='dbo' )

BEGIN

DECLARE @CONSTRAINT_NAME NVARCHAR(1000)

SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = '' AND TABLE_SCHEMA ='dbo'

DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE [table name] DROP CONSTRAINT ' + @CONSTRAINT_NAME;

EXEC (@sql);

END

TO DELETE FOREIGN KEY

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = '[table name]' AND TABLE_SCHEMA ='dbo' )

BEGIN

DECLARE @CONSTRAINT_NAME NVARCHAR(1000)

SELECT TOP 1 @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = '[table name]' AND TABLE_SCHEMA ='dbo'

DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE [table name] DROP CONSTRAINT ' + @CONSTRAINT_NAME;

EXEC (@sql);

END

Note : You can use it iterative or find the number of forign keys

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R7y ago
Hi,
Try this code:
ALTER TABLE DROP CONSTRAINT
0
Code Alone9y ago
Hi,

As per your requirement you can write Stored Procedure for the Operation, And pass your parameters as well. And use that on your Form. Here I am providing you the solution(Query) .
To DELETE PRIMARY KEY
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = '[table name]' AND TABLE_SCHEMA ='dbo' )
BEGIN
DECLARE @CONSTRAINT_NAME NVARCHAR(1000)
SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = '' AND TABLE_SCHEMA ='dbo'
DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE [table name] DROP CONSTRAINT ' + @CONSTRAINT_NAME;
EXEC (@sql);
END

TO DELETE FOREIGN KEY
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = '[table name]' AND TABLE_SCHEMA ='dbo' )
BEGIN
DECLARE @CONSTRAINT_NAME NVARCHAR(1000)
SELECT TOP 1 @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = '[table name]' AND TABLE_SCHEMA ='dbo'
DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE [table name] DROP CONSTRAINT ' + @CONSTRAINT_NAME;
EXEC (@sql);
END

Note : You can use it iterative or find the number of forign keys

+1