Breaking News: Grepper is joining You.com. Read the official announcement!

Remove Constraints from a database table

Add Answer

Handsome Horse answered on February 19, 2021 Popularity 3/10 Helpfulness 1/10

answer Remove Constraints from a database table

related drop table with constraints

related drop constraints (SQL Server)

Remove Constraints from a database table

Comment

DECLARE @sql NVARCHAR(MAX) = '';

-- Remove foreign key constraints from Students table that reference Classes
SELECT @sql += 'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) 
    + ' DROP CONSTRAINT ' + QUOTENAME(FK.CONSTRAINT_NAME) + '; '
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE FK.TABLE_NAME = 'Students' AND C.UNIQUE_CONSTRAINT_NAME IN (
    SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'Classes'
);

-- Remove foreign key constraints from Classes table that reference Students
SELECT @sql += 'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) 
    + ' DROP CONSTRAINT ' + QUOTENAME(FK.CONSTRAINT_NAME) + '; '
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE FK.TABLE_NAME = 'Classes' AND C.UNIQUE_CONSTRAINT_NAME IN (
    SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'Students'
);

EXEC sp_executesql @sql;

Popularity 3/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: constraints database-table sql

Link to this answer
Share Copy Link

Contributed on Feb 10 2024

Handsome Horse

0 Answers Avg Quality 2/10

Closely Related Answers

drop table with constraints

Comment

1. 'First Drop contrstraints like this'
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
2. 'Then drop table'
DROP TABLE table_name;

Popularity 9/10 Helpfulness 6/10 Language sql

Source: Grepper

Tags: constraints cons

Link to this answer
Share Copy Link

Contributed on Feb 19 2021

Expensive Eagle

0 Answers Avg Quality 2/10

drop constraints (SQL Server)

Comment

ALTER TABLE Azure_Policy_User DROP CONSTRAINT FK_Azure_Policy_User_UserID;

Popularity 6/10 Helpfulness 2/10 Language sql

Source: chat.openai.com

Tags: constraints drop sql

Link to this answer
Share Copy Link

Contributed on Oct 26 2023

Stormy Sheep

0 Answers Avg Quality 2/10

Remove Constraints from a database table

Contents

More Related Answers

Remove Constraints from a database table

Closely Related Answers

drop table with constraints

drop constraints (SQL Server)

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.