<p>Is there a way to enforce constraint checking in MSSQL only when inserting new rows?  I.e. allow the constraints to be violated when removing/updating rows?</p>

<p>Update: I mean FK constraint.</p>

[{'answer_id': 38892, 'author': 'Jon Limjap', 'author_id': 372, 'author_profile': 'https://Stackoverflow.com/users/372', 'pm_score': 1, 'selected': False, 'text': '<p>I think your best bet is to remove the explicit constraint and add a <a href="http://msdn.microsoft.com/en-us/library/ms180169.aspx" rel="nofollow noreferrer">cursor</a> for inserts, so you can perform your checking there and raise an error if the constraint is violated.</p>\n'}, {'answer_id': 38897, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 4, 'selected': True, 'text': "<p>You could create an INSERT TRIGGER that checks that the conditions are met. That way all updates will go straight through.</p>\n\n<pre><code>CREATE TRIGGER employee_insupd\nON employee\nFOR INSERT\nAS\n/* Get the range of level for this job type from the jobs table. */\nDECLARE @min_lvl tinyint,\n   @max_lvl tinyint,\n   @emp_lvl tinyint,\n   @job_id smallint\nSELECT @min_lvl = min_lvl, \n   @max_lvl = max_lvl, \n   @emp_lvl = i.job_lvl,\n   @job_id = i.job_id\nFROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id \n   JOIN jobs j ON j.job_id = i.job_id\nIF (@job_id = 1) and (@emp_lvl &lt;&gt; 10) \nBEGIN\n   RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)\n   ROLLBACK TRANSACTION\nEND\nELSE\nIF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)\nBEGIN\n   RAISERROR ('The level for job_id:%d should be between %d and %d.',\n      16, 1, @job_id, @min_lvl, @max_lvl)\n   ROLLBACK TRANSACTION\nEND\n</code></pre>\n"}, {'answer_id': 38928, 'author': 'Matt', 'author_id': 4154, 'author_profile': 'https://Stackoverflow.com/users/4154', 'pm_score': 1, 'selected': False, 'text': '<p>What sort of constraints?  I\'m guessing foreign key constraints, since you imply that deleting a row might violate the constraint.  If that\'s the case, it seems like you don\'t really need a constraint per se, since you\'re not concerned with referential integrity.</p>\n\n<p>Without knowing more about your specific situation, I would echo the intent of the other posters, which seems to be "enforce the insert requirements in your data access layer".  However, I\'d quibble with their implementations.  A trigger seems like overkill and any competent DBA should sternly rap you on the knuckles with a wooden ruler for trying to use a cursor to perform a simple insert.  A stored procedure should suffice.</p>\n'}]
['https://Stackoverflow.com/questions/38890', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3153/']