|
|
Posted by Tony Rogerson on 11/12/06 19:13
Oh - my - god; and how many levels does "etc..." cover in your example?
Also, it still doesn't properly validate for an email address - post some
workable, essentially you can't, its not practical nor possible in Standard
SQL as implemented by Microsoft SQL Server to date.
The fact that you would really sacrifice performance, scalability and
concurrency just to be so anal in not using tried, tested and recommend
approaches for complex data validation in SQL Server 2005 (CLR) speaks
volumes; I sure hope you don't recommend these types of solutions to "your
clients", if so - let me have a list of those on SQL Server so I can go and
fix their performance and concurrency problems, the saving in hardware and
application rewrite costs alone; my fee is a fraction to what they will save
and I'm not cheap - well, I charge tripple what you do if that's any
indicator.
Like I say, get out of your class room, get some real industrial experience
and do us all a favour.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1163345438.382735.291580@h48g2000cwc.googlegroups.com...
>>> 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.
>
Navigation:
[Reply to this message]
|