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

Glenn%20Barber's gravatar image

Glenn Barber
1.1k234052
accept rate: 0%

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

ALTER TABLE myTable NOCHECK CONSTRAINT myFkConstraint;
...
ALTER TABLE myTable CHECK CONSTRAINT myFkConstraint;
(07 Dec '12, 03:23) Volker Barth

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

(08 Dec '12, 12:15) Glenn Barber
Replies hidden

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

(10 Dec '12, 09:38) Volker Barth

permanent link

answered 06 Dec '12, 15:46

Breck%20Carter's gravatar image

Breck Carter
27.0k447618889
accept rate: 21%

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...

(07 Dec '12, 03:08) Volker Barth
1

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

(07 Dec '12, 06:14) Markus Dütting

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.

permanent link

answered 09 Dec '12, 15:01

Breck%20Carter's gravatar image

Breck Carter
27.0k447618889
accept rate: 21%

edited 09 Dec '12, 15:05

...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...

(10 Dec '12, 09:36) Volker Barth

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

(14 Dec '12, 03:32) Volker Barth
1

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...)...

(18 Dec '12, 09:08) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×61
×26
×4

question asked: 06 Dec '12, 15:39

question was seen: 1,571 times

last updated: 18 Dec '12, 09:09