• MS SQL does not really support this.

    This is not a typical thing to do. I remember seeing a product that someone sells to do this. I cannot remember what it is called.

    You can use a combination of check constraints and triggers (or just triggers) to do this. Check constraints are generally against just the single table, but they can execute functions and functions can look at other tables and even be cross-database.

    I would recommend doing this sparingly and be careful to see what it is doing to your database performance. I have implemented it a few times (and am actually implementing it on an application being deployed now), but it takes some careful planning or it can lead to problems.

    I tend to put every bit of data integrity I possibly can into indexes, constraints, and triggers (triggers only when absolutely necessary) rather than doing the same thing in application layers or in stored procedures. This can be argued against for a lot of reasons - sone of them pretty good reasons at times, but I have found a couple of things true.

    First, if you are doing something in an insert or update stored procedure that relates to data integrity, you can probably do the same thing with constraints or triggers and get the same performance at the same time eliminating the ability to bypass the logic by not using the stored procedures.

    The second thing is that there will be a time in which your data will need to be modified with a tool other than the application with all of your business logic designed for your data. Argue all you want, but some new thing will come up in which someone needs to bulk load a bunch of data with SSIS or update a group of records with a query and the dope that does it will not know about the business rules programmed into the application. It would be nice to not hire that guy in the first place, but it is sometimes me, so I have to forgive him.

    Please, nobody beat me up on this one. Your argument is good and I agree with many of your points.