CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mohamed
Mohamed

Reputation: 1

NOT to soft delete a row in a table that contain foreign key to other tables

I am doing soft delete in all my tables. I should not soft delete a row when that ID is referred in other tables. Also, when the referred child table is deleted i.e, Isdeleted = 1, then I should be able to soft delete the parent record.

Your suggestions are appreciated.

Upvotes: 0

Views: 387

Answers (1)

John Dewey
John Dewey

Reputation: 7093

You'll need to generate some dynamic sql from the schema. I don't have time to pivot all the items into dynamic sql here, but you could use either a cursor loop or possibly PIVOT for the dynamic SQL:

-- parent/child schemas, tables, columns
select parent_schema=p.TABLE_SCHEMA
, parent_table=p.TABLE_NAME
, parent_pk_column=p.COLUMN_NAME
, child_schema=c.TABLE_SCHEMA
, child_table=c.TABLE_NAME
, child_fk_column=c.COLUMN_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE p
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS pc
    on pc.UNIQUE_CONSTRAINT_SCHEMA=p.CONSTRAINT_SCHEMA
    and pc.UNIQUE_CONSTRAINT_NAME=p.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
    on c.CONSTRAINT_SCHEMA=pc.CONSTRAINT_SCHEMA
    and c.CONSTRAINT_NAME=pc.CONSTRAINT_NAME
where exists(
    select 1 from INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME='IsDeleted' and TABLE_SCHEMA=p.TABLE_SCHEMA and TABLE_NAME=p.TABLE_NAME
)

-- tables/pk columns having IsDeleted column but no children
select parent_schema=p.TABLE_SCHEMA
, parent_table=p.TABLE_NAME
, parent_column=p.COLUMN_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE p
where not exists(
    select 1 from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    where UNIQUE_CONSTRAINT_SCHEMA=p.CONSTRAINT_SCHEMA
    and UNIQUE_CONSTRAINT_NAME=p.CONSTRAINT_NAME
)
and exists(
    select 1 from INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME='IsDeleted' and TABLE_SCHEMA=p.TABLE_SCHEMA and TABLE_NAME=p.TABLE_NAME
)

Upvotes: 1

Related Questions