Applicable for SQL server only:
Resolve constraint violation error:
To invalidate or disable all the constraint execute against SSMS
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";
Check if constraint are enabled or not
Select
Schema_name(Schema_id) as SchemaName,
object_name(Parent_object_id) as TableName,
name as ForeignKeyConstraintName,
Case When Is_disabled=1 Then 'No'
ELSE 'Yes' End as IsEnabled
from sys.foreign_keys
Validate the constraint:
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL";
It will fail
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_PAR_CRON_TRIGGERS_CHL_CRON_TRIGGERS".
The conflict occurred in database "demo06947", table "dbo.CHL_CRON_TRIGGERS".
delete the record:
select SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP from dbo.CHL_CRON_TRIGGERS a
where TRIGGER_NAME NOT IN(select TRIGGER_NAME from dbo.PAR_CRON_TRIGGERS)
Create backup table before deletion
select SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP INTO [demo06947].dbo.temp_constraint_CRON_TRIGGERS
from dbo.CHL_CRON_TRIGGERS a where TRIGGER_NAME NOT IN(select TRIGGER_NAME from dbo.PAR_CRON_TRIGGERS)
Some SQL server useful views:
select containment,containment_desc,is_in_standby,state_desc from sys.databases
