Is there a way to temporarily turn off RI during batch load or update processes?

[glenn_barber]

We have some cases when seting up data in conversion where it would be handy to be able to turn off RI on specific tables (or generally) while importing data. We would like to do this without having to drop the foreign keys or RI - and after everything is in place then reactivate the RI.

This would be an alternative to generating scripts for dropping all the RI then recreating the RI afterwards.

I understand that this is a feature of MS SQL Server.

[VolkerBarth]

You are refering to the following MS SQL feature, right?

ALTER TABLE myTable NOCHECK CONSTRAINT myFkConstraint;
...
ALTER TABLE myTable CHECK CONSTRAINT myFkConstraint;

[glenn_barber]

Yes - this is the one - some of our engineers who have worked on MS SQL complain about not having this feature

[VolkerBarth]

I hope they are aware of that feature's possibilities, as Breck has pointed out.

[Breck_Carter]

SQL Anywhere does not have any feature quite like NOCHECK CONSTRAINT, which is different from dropping and recreating the foreign key constraint as follows...

NOCHECK CONSTRAINT does not check the new foreign key column values when CHECK CONSTRAINT is executed; here is a demonstration using SQL Server 2008:

CREATE TABLE parent (
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT parent_pkey PRIMARY KEY ( parent_key ) )
GO

CREATE TABLE child (
   child_key    INTEGER NOT NULL,
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT child_pkey PRIMARY KEY ( child_key ),
   CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key ) )
GO

ALTER TABLE child NOCHECK CONSTRAINT fkey
GO

INSERT child VALUES ( 99, 2, 'child' )
GO
INSERT parent VALUES ( 1, 'parent' )
GO

ALTER TABLE child CHECK CONSTRAINT fkey
GO

SELECT * FROM parent
GO
SELECT * FROM child
GO

-----

1> SELECT * FROM parent
2> GO
 parent_key  data
 ----------- ----------
           1 parent

(1 row affected)
1> SELECT * FROM child
2> GO
 child_key   parent_key  data
 ----------- ----------- ----------
          99           2 child

In other words, NOCHECK CONSTRAINT allows permanent violations of referential integrity to creep into your database. You might think you are protected because future inserts are checked...

1> INSERT child VALUES ( 98, 3, 'child' )
2> GO
Msg 547, Level 16, State 1, Server ENVY, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "fkey". The
conflict occurred in database "test", table "dbo.parent", column 'parent_key'.
The statement has been terminated.

...but the old garbage is still there.

[VolkerBarth]

...interesting "feature" - and no, I don't want suggest to port that to SQL Anywhere, too - I'd rather use the "DROP FK - insert and fix stuff - ADD FK" process:)

Oh, yes - the signature motto "Watcom does the things the way they should be done" does make a difference...

[VolkerBarth]

...and now Breck has blogged en detail comparing SQL Anywhere's and MS SQL Server's features here...

[VolkerBarth]

AFAIK, MS SQL allows to check the re-enabled constraints, too, however that requires an additional WITH CHECK clause (yes, you can never do enough CHECKing):

ALTER TABLE child WITH CHECK CHECK CONSTRAINT fkey
GO

should fail if there are existing RI violations.

Nevertheless, I agree that this comes quite unexpected (and looks - well, not too well-engineered...)...

[Breck_Carter]

See the wait_for_commit option: http://dcx.sybase.com/index.html#1201/en/dbadmin/wait-for-commit-option.html

You might also benefit from bulk load: http://dcx.sybase.com/index.html#1201/en/dbadmin/runninginspecialmodes.html

[VolkerBarth]

CAVEAT: "wait_for_commit" does delay the RI checks until you are COMMITing - and if you're using LOAD TABLE to bulk-insert, that does its own automatic commit, so this won't help you to say, load two tables, fix RI violations, and then try to commit - there will have been several commits in-between. So you might need to use INSERT - or load into temp tables without RI declarations, fix data, and then bulk-insert into the real tables...

[Former Member]

Regarding LOAD TABLE - "For local temporary tables, a commit is not performed."