Posted by --CELKO-- on 11/12/06 15:30

>> Are you going to finally admit you are wrong and that the only reasonable way to validate an email address in a CHECK CONSTRAINT is CLR? <<

The way you do it in a CHECK() is with a SIMILAR TO predicate, as per
ANSI/ISO Standard SQL and not by violating Codd's rules. (12. The
nonsubversion rule: If the system provides a low-level
(record-at-a-time) interface, then that interface cannot be used to
subvert the system (e.g.) bypassing a relational security or integrity
constraint.)

The way you do it in SQL Server (until they come up to Standard SQL) is
with a CREATE VIEW .. WITH CHECK OPTION;

If you do not know the trick, let me give a simple example. I have an
adjacency list model tree and I want to enforce the rule that (number
of nodes) = (number of edges) -1, which is a necessary but not
sufficient condition for a tree.

-- Standard SQL
CREATE TABLE Tree
(parent_node CHAR(1), -- null is root
child_node CHAR(1) NOT NULL,
UNIQUE (parent_node, child_node),
CONSTRAINT no_simple_loops
CHECK (parent_node <> child_node), -- easy table level constaint
CONSTRAINT no_extra_edges
CHECK ((SELECT COUNT(*) FROM Tree) - 1
= (SELECT COUNT(*)
FROM ((SELECT parent_node FROM Tree)
UNION
(SELECT child_node FROM Tree)) AS X(node)
),
etc.);

--SQL Server workaround

CREATE VIEW MyTree (parent_node, child_node)
AS
SELECT T1.parent_node, T1.child_node
FROM Tree AS T1
WHERE NOT EXISTS
(((SELECT COUNT(*) FROM Tree) - 1
<> ((SELECT COUNT(*)
FROM ((SELECT parent_node FROM Tree)
UNION
(SELECT child_node FROM Tree)) AS X(node)
)
WITH CHECK OPTION;

I would put the nodes in a VIEW or a CTE since that could be useful in
other places. Then use DCL to keep users away from the basr table.
This converts a complex CHECK() with subqueries into a WHERE clause.
It is a standard SQL programming trick that goes back over a decade ago
and was popuilar with DB2 programmers.

[Back to original message]