M359 - Constraint Checking

Just had an interesting, but inconclusive, FirstClass conversation regarding turning off Constraint Checking while performing a bulk load of data into a database. My view was that we have constraint checking for a very good reason: ensuring that the database remains consistent - because as soon as the data is inconsistent it then become unreliable, and if its unreliable then people will not use or trust it. So its pretty important!.

So the idea of turning off constraint checking during a bulk load sounds almost like suicide to me. But, it was pointed out that during a bulk load you are probably populating one table at a time, and if the table that a constraint depends upon is currently empty then the import will fail. So you HAVE to turn off constraint checking. A side effect is that it speeds up the import - well, to me that would be a side effect, but it has been suggested elsewhere that it might be considered to be a primary motive (!?).

But what really bothers me here, and so far no one has been able to convince me otherwise, is that if the data you are importing comes from another source over which you have no control, how do you know that the data is already consistent? And if it isn't consistent, and you have constraint checking turned off, you now have an inconsistent database! That can't be right.

One of the contributers to the discussion said that when constraint checking is turn back on after the import, error messages will be issued for any constraint that fails. I guess this is some help.

I'm still not convinced - there must be more to this.