|
|
Posted by markjerz on 01/16/07 11:01
Hi Erland,
I am now having problems updating the rule to allow nulls. It's
basically telling me that it can not drop the rule because it is bound
to one or more columns.
I'm not actually dropping the rule just opening it's properties in
enterprise manager and then clicking apply once I've changed it.
Any ideas? It seems a little stupid that I would have to unbind it
first then change it and rebind it. It's currently bound to a lot of
columns.
Mark
Erland Sommarskog wrote:
> (markjerz@googlemail.com) writes:
> > Now, table2 has a rule on various columns:
> >
> > @CHARACTER IN ('Y','N')
> >
> > but the column allows nulls, in the design view is says so anyway.
> >
> > When I run this query I get:
> >
> > A column insert or update conflicts with a rule imposed by a previous
> > CREATE RULE statement. The statement was terminated. The conflict
> > occurred in database 'database', table 'table', column 'column'.
> > The statement has been terminated.
> >
> > Obviously, I've changed the names of everything.
> >
> > The only data in those columns which could possibly conflict with the
> > rule is the NULL value. Any ideas why this doesn't work?
>
> In my opinion it is a bug, but it has been way since SQL 7 (SQL 6.5
> did it right), and Microsoft considers rules to be a deprecated feature,
> so I have no hope for a fix.
>
> The workaround is to write the rule so that it explicitly permits NULL.
>
> As for the deprecation - binding rules directly to table columns is a poor
> idea; use constraints instead. However, in my opinion Microsoft has
> failed to understand that binding rules to user-defined types is an
> asset, and for which they do not have any replacement.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|