Check Constraints can help enforce the all or nothing approach when it comes to column updates

,

If you have a set of columns inside your table that you want to allow nulls in, however if one of the columns is updated force all columns in the set to be updated, use a check constraint. In my case, I had 3 columns for delete info, which were nullable. However, if one value was updated in there, I want all three of the delete columns to require updating. I created the script below to generate the creation and removal of these constraints on a list of tables.

/*******************************************************
CHECK CONSTRAINT TO ENSURE SET OF COLUMNS IS NULL OR IF UPDATED, THAT ALL COLUMNS IN SET ARE UPDATED
Columns: 
delete_date    null
delete_by_id   null
delete_comment null
PASS CONDITION 1: 
IF ALL COLUMNS NULL = PASS
PASS CONDITION 2:
IF ALL COLUMNS ARE UPDATED/NOT NULL = PASS
FAIL: 
IF 1,2 OF THE COLUMNS ARE UPDATED, BUT NOT ALL 3 THEN FAIL
*******************************************************/
/*******************************************************
GENERATE CHECK CONSTRAINT ON ALL SELECTED TABLES TO REQUIRE 
ALL DELETE DATE COLUMNS TO BE UPDATED CORRECTLY
*******************************************************/
select
	table_schema
	,table_name
	,script_to_remove_if_exists = '
	IF exists (select * from sys.objects where name =''check_' + table_schema + '_' + table_name + '_softdelete_requires_all_delete_columns_populated_20130718'')
	begin
	alter table ' + table_schema + '.' + table_name + ' drop constraint check_' + table_schema + '_' + table_name + '_softdelete_requires_all_delete_columns_populated_20130718
	end 
	'
	,script_to_run =
	'
	alter table ' + table_schema + '.' + table_name + ' add constraint check_' + table_schema + '_' + table_name + '_softdelete_requires_all_delete_columns_populated_20130718 check (
	(
	case
		when delete_date is not null then 1
		else 0
	end
	+ case
			when delete_by_id is not null then 1
			else 0
		end
	+ case
			when delete_comment is not null then 1
			else 0
		end
	) in (0, 3)
) 
'
from
	INFORMATION_SCHEMA.TABLES t
where
	table_name like 'mytablename%' 
	and exists (
		select
			*
		from
			INFORMATION_SCHEMA.COLUMNS C
		where
			t.TABLE_CATALOG = C.TABLE_CATALOG
			and t.TABLE_SCHEMA = C.TABLE_SCHEMA
			and t.TABLE_NAME = C.TABLE_NAME
			and C.COLUMN_NAME = 'delete_by_id'
	)
	and exists (
		select
			*
		from
			INFORMATION_SCHEMA.COLUMNS C
		where
			t.TABLE_CATALOG = C.TABLE_CATALOG
			and t.TABLE_SCHEMA = C.TABLE_SCHEMA
			and t.TABLE_NAME = C.TABLE_NAME
			and C.COLUMN_NAME = 'delete_comment'
	)
	and exists (
		select
			*
		from
			INFORMATION_SCHEMA.COLUMNS C
		where
			t.TABLE_CATALOG = C.TABLE_CATALOG
			and t.TABLE_SCHEMA = C.TABLE_SCHEMA
			and t.TABLE_NAME = C.TABLE_NAME
			and C.COLUMN_NAME = 'delete_date'
	)
order by
	TABLE_SCHEMA asc
go

 

 

Rate

Share

Share

Rate