SQL Server Deletes a table foreign key constraint Error [Solved]

  Create two tables tbltesta and tbltesb, and delete tbltesta


			create table dbo.TblTestA(
			Id					bigint			primary key identity(1, 1),
			name			varchar(20)     unique,
		    )
	
			create table dbo.TblTestB(
			Id					bigint			primary key identity(1, 1),
			TestAId				bigint			not null foreign key references 
			Status				varchar(1),
	     	)

Error reported: it is referenced by a foreign key constraint

Reason: other tables refer to the foreign key of this table

Solution:

1. Find the foreign key of this table referenced by other tables

exec sp_Helpconstraint ‘table name’;

2. Find the associated foreign key constraint table name

select name
from   sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id
where f.parent_object_id=object_ID (‘foreign key association table name ‘)

3. Delete the foreign key constraint

Alter table foreign key constraint table, Drop constraint name (constraint name found above)


4. Delete table (events can be added)

BEGIN TRY
    BEGIN TRANSACTION
        ALTER TABLE TblTestB DROP CONSTRAINT FK__TblTestB__TestAI__635CD8E4
        DROP TABLE dbo.TblTestA
    COMMIT TRANSACTION
        PRINT 'commit sucesss'
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT 'rollback sucesss'
END CATCH

 

Read More: