StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POImplementing check constraints with SQL CLR integration
text
Body
copied!<p>I'm implementing 'check' constraints that simply call a CLR function for each constrained column.</p> <p>Each CLR function is one or two lines of code that attempts to construct an instance of the user-defined C# data class associated with that column. For example, a "Score" class has a constructor which throws a meaningful error message when construction fails (i.e. when the score is outside a valid range).</p> <p><strong>First, what do you think of that approach?</strong> For me, it centralizes my data types in C#, making them available throughout my application, while also enforcing the same constraints within the database, so it prevents invalid manual edits in management studio that non-programmers may try to make. It's working well so far, although updating the assembly causes constraints to be disabled, requiring a recheck of all constraints (which is perfectly reasonable). I use <code>DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS</code> to make sure the data in all tables is still valid for enabled and disabled constraints, making corrections as necessary, until there are no errors. Then I re-enable the constraints on all the tables via <code>ALTER TABLE [tablename] WITH CHECK CHECK CONSTRAINT ALL</code>. <strong>Is there a T-SQL statement to re-enable with check all check constraints on ALL tables, or do I have to re-enable them table by table?</strong></p> <p>Finally, for the CLR functions used in the check constraints, I can either:</p> <ol> <li>Include a try/catch in each function to catch data construction errors, returning false on error, and true on success, so that the CLR doesn't raise an error in the database engine, or...</li> <li>Leave out the try/catch, just construct the instance and return true, allowing that aforementioned 'meaningful' error message to be raised in the database engine.</li> </ol> <p>I prefer 2, because my functions are simpler without the error code, and when someone using management studio makes an invalid column edit, they'll get the meaningful message from the CLR like <code>"Value for type X didn't match regular expression '^p[1-9]\d?$'"</code> instead of some generic SQL error like "constraint violated". <strong>Are there any severe negative consequences of allowing CLR errors through to SQL Server, or is it just like any other insert/update failure resulting from a constraint violation?</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload