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.
asked 06 Dec '12, 15:39
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
answered 06 Dec '12, 15:46
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.