I have posted a few ideas on implementing business rules in the
database:

http://sqlblog.com/blogs/alexander_k...ql-server.aspx

I am very interested in feedback.

Cheers,

Alex Kuznetsov,
SQL Server MVP

AK wrote:

So you really just want general constraints like in ANSI/ISO SQL92.
The standard syntax is CREATE ASSERTION.

I agree that any SQL DBMS would benefit from the ability to enforce
general constraints.

--
Roy

Roy Hann wrote:

There is that, but I was also going to suggest the OP read the
Lorentzos/Date/Darwen book on Temporal Data.

The specific example he gave involves foreign key references to an
interval data type--one for which the DURING keyword might seem strange
from an english perspective but perfectly acceptable from a formal
perspective.

I would appreciate CREATE ASSERTION statements too. This would be a
more genric tool than putting this into a FOREIGN KEY constraint. Not
all the rules you might want to enforce involve a foreign key. Also
this would be according to the SQL standard.

To allow subqueries (also from diff. tables) in CHECK constraints
would also be interesting.

brgds

Philipp Post

A quick comment, SQL-92 allows CHECK() constraints to have any valid
search condition, evern those that referencs other tables or its own
table at aggregate levels. But all CHECK() constraints are TRUE for
an empty table.

The reason we defined the CREATE ASSERTION was that it is at the
schema level and it works with empty tables. This is why CHECK()
constraint names are global to the schema.