StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POValidating UPDATE and INSERT statements against an entire table
text
Body
copied!<p>I'm looking for the best way to go about adding a constraint to a table that is effectively a unique index on the <em>relationship</em> between the record and the rest of the records in that table.</p> <p>Imagine the following table describing the patrols of various guards (from the previous watchman scenario)</p> <pre><code>PK PatrolID Integer FK GuardID Integer Starts DateTime Ends DateTime </code></pre> <p>We start with a constraint specifying that the start and end times must be logical:</p> <pre><code>Ends >= Starts </code></pre> <p>However I want to add another logical constraint: A specific guard (GuardID) cannot be in two places at the same time, meaning that for any record the period specified by Start/Ends should not overlap with the period defined for any other patrol by the same guard.</p> <p>I can think of two ways of trying to approach this:</p> <p>Create an INSTEAD OF INSERT trigger. This trigger would then use cursors to go through the INSERTED table, checking each record. If any record conflicted with an existing record, an error would be raised. The two problems I have with this approach are: I dislike using cursors in a modern version of SQL Server, and I'm not sure how to go about implimenting the same logic for UPDATEs. There may also be the complexity of records within INSERTED conflicting with each other.</p> <p>The second, seemingly better, approach would be to create a CONSTRAINT that calls a user defined function, passing the PatrolID, GuardID, Starts and Ends. The function would then do a WHERE EXISTS query checking for any records that overlap the GuardID/Starts/Ends parameters that are not the original PatrolID record. However I'm not sure of what potential side effects this approach might have.</p> <p>Is the second approach better? Does anyone see any pitfalls, such as when inserting/updating multiple rows at once (here I'm concerned because rows within that group could conflict, meaning the order they are "inserted" makes a difference). Is there a better way of doing this (such as some fancy INDEX trick?)</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