Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I drop all foreign-key constraints on a table in Sql Server 2000?

How do I drop all foreign-key constraints on a table in SQL Server 2000 using T-SQL?

like image 859
Mathias Avatar asked Sep 17 '09 12:09

Mathias


People also ask

How drop all foreign key constraints in a table?

To drop a foreign key from a table, use the ALTER TABLE clause with the name of the table (in our example, student ) followed by the clause DROP CONSTRAINT with the name of the foreign key constraint. In our example, the name of this constraint is fk_student_city_id .

How do I remove a foreign key constraint from a table in SQL Server?

To delete a foreign key constraint In Object Explorer, expand the table with the constraint and then expand Keys. Right-click the constraint and then click Delete.

Which SQL command is used to remove foreign key constraint?

The DROP CONSTRAINT command is used to delete a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint.


2 Answers

If simply disabling constraints is an option here, you can use:

ALTER TABLE myTable NOCHECK CONSTRAINT all

then you can switch them back on simply using:

ALTER TABLE myTable WITH CHECK CHECK CONSTRAINT all

If you want to disable constrains in all tables you can use:

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

More in the question: Can foreign key constraints be temporarily disabled using TSQL?

But if you need to drop constraints permanently you can use this script posted on databasejurnal.com.

Just modify it slightly to only drop the foreign keys

create proc sp_drop_fk_constraints
    @tablename  sysname
as
-- credit to: douglas bass

set nocount on

declare @constname  sysname,
    @cmd        varchar(1024)

declare curs_constraints cursor for
    select  name
    from    sysobjects 
    where   xtype in ('F')
    and (status & 64) = 0
    and     parent_obj = object_id(@tablename)

open curs_constraints

fetch next from curs_constraints into @constname
while (@@fetch_status = 0)
begin
    select @cmd = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constname
    exec(@cmd)
    fetch next from curs_constraints into @constname
end

close curs_constraints
deallocate curs_constraints

return 0
like image 136
kristof Avatar answered Sep 29 '22 10:09

kristof


Here you go: (not tested on SQL2000, but should be ok)

Generates 'disables':

SELECT  'IF EXISTS (SELECT * FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N''[dbo].' + FK +''') 
   AND parent_object_id = OBJECT_ID(N''[dbo].' + PT + ''')) 
   ALTER TABLE ' + PT + ' NOCHECK CONSTRAINT ' + FK + ';'
FROM 
(SELECT 
    OBJECT_NAME(constraint_object_id) as FK,
    OBJECT_NAME(parent_object_id) as PT
    FROM [sys].[foreign_key_columns] ) T
ORDER BY FK

Generates 'enables':

SELECT  'ALTER TABLE ' + PT + ' WITH CHECK CHECK CONSTRAINT ' + FK + ';'
FROM 
(SELECT 
    OBJECT_NAME(constraint_object_id) as FK,
    OBJECT_NAME(parent_object_id) as PT
    FROM [sys].[foreign_key_columns] ) T
ORDER BY FK

Update: Oops, I thought you wanted it for all tables :) You can just modify above for your single table.

like image 28
leppie Avatar answered Sep 29 '22 12:09

leppie