Is there a way to disable and re enable FOREIGN KEY constraints for a database Azure SQL

Question

I'm trying to truncate some tables in a database. Some of these tables have foreign keys. So when I try to truncate them I get the following error.

Cannot truncate table 'IDN_OAUTH2_ACCESS_TOKEN' because it is being referenced by a FOREIGN KEY constraint.

Is there a way to disable FOREIGN KEY constraints in Azure SQL (Microsoft SQL Azure (RTM) - 12.0.2000.8) and re-enable them? In MySQL, I have done the same using the following script.

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE IDN_OAUTH2_ACCESS_TOKEN;

SET FOREIGN_KEY_CHECKS=1;

Duplicate for https://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql — Hemant Halwai, Feb 05 '21 at 18:51
Hi@Buddhima Udaranga , please correct me if I understand you wrong. — Joseph Xu, Feb 08 '21 at 14:34

score 2 · Accepted Answer · answered Feb 08 '21 at 02:05

I think we can write some T-SQL scripts to achieve that. Using T-SQL to splice T-SQL(add & drop FK index) commands.

We can use following script to query all the FK and then copy the query result into a text. We can use the query result to rebuild foreign key index.

select
   concat(concat('alter table ',c.CONSTRAINT_SCHEMA),concat('.',fk.TABLE_NAME)),
   concat(' add constraint  ', c.CONSTRAINT_NAME),  --cu.COLUMN_NAME
   concat(' foreign key( ',cu.COLUMN_NAME),
   concat(concat(') references ',c.CONSTRAINT_SCHEMA),concat('.',pk.TABLE_NAME)),
   concat(concat('(',pt.COLUMN_NAME),');')
from
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
    on c.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
    on c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
    on c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
inner join  (
            select
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            from
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                on i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            where
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    on pt.TABLE_NAME = pk.TABLE_NAME

We can replace the add constraint to drop constraint. As follows:


select
   concat(concat('alter table ',c.CONSTRAINT_SCHEMA),concat('.',fk.TABLE_NAME)),
   concat(' drop constraint  ', c.CONSTRAINT_NAME),  --cu.COLUMN_NAME
   concat(' foreign key( ',cu.COLUMN_NAME)
from
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
    on c.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
    on c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
    on c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
inner join  (
            select
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            from
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                on i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            where
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    on pt.TABLE_NAME = pk.TABLE_NAME

So you can drop FKs and after truncate some tables, then rebuid FKs.

Is there a way to disable and re enable FOREIGN KEY constraints for a database Azure SQL

1 Answers1