There are multiple ways to disable constraints in Oracle. constraint_name; Another way to enable and disable constraints in Oracle would be to either use a plsql block or write a script. Execute Immediate 'alter table '||:tab_name||' disable constraint '||tabCons(numCount);
To disable foreign key constraints: DECLARE @sql nvarchar(max) = N''; ;WITH x AS ( SELECT DISTINCT obj = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '. ' + QUOTENAME(OBJECT_NAME(parent_object_id)) FROM sys. foreign_keys ) SELECT @sql += N'ALTER TABLE ' + obj + N' NOCHECK CONSTRAINT ALL; ' FROM x; EXEC sys.
-- Disable the constraints on a table called tableName:
ALTER TABLE tableName NOCHECK CONSTRAINT ALL
-- Re-enable the constraints on a table called tableName:
ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL
---------------------------------------------------------
-- Disable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
-- Re-enable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
---------------------------------------------------------
You can disable FK and CHECK constraints only in SQL 2005+. See ALTER TABLE
ALTER TABLE foo NOCHECK CONSTRAINT ALL
or
ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column
Primary keys and unique constraints can not be disabled, but this should be OK if I've understood you correctly.
And, if you want to verify that you HAVEN'T broken your relationships and introduced orphans, once you have re-armed your checks, i.e.
ALTER TABLE foo CHECK CONSTRAINT ALL
or
ALTER TABLE foo CHECK CONSTRAINT FK_something
then you can run back in and do an update against any checked columns like so:
UPDATE myUpdatedTable SET someCol = someCol, fkCol = fkCol, etc = etc
And any errors at that point will be due to failure to meet constraints.
You can actually disable all database constraints in a single SQL command and the re-enable them calling another single command. See:
I am currently working with SQL Server 2005 but I am almost sure that this approach worked with SQL 2000 as well
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With