Inserting nulls in to table conflicting with rule

[markjerz]

Hi,

I basically have two tables with the same structure. One is an archive
of the other (backup). I want to essentially insert the data in to the
other.

I use:

INSERT INTO table ( column, column .... )
SELECT * FROM table2

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?

Thanks.

[Erland Sommarskog]

(ma******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Michael Austin]

ma******@googlemail.com wrote:

Hi,

I basically have two tables with the same structure. One is an archive
of the other (backup). I want to essentially insert the data in to the
other.

I use:

INSERT INTO table ( column, column .... )
SELECT * FROM table2

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?

Thanks.

my guess is by using the rule (IN ('Y','N')) - and that because you have said
ONLY Y,N values are allowed. It is essentially a check constraint in other
databases. And I would consider any other behavior a bug.

--
Michael Austin
Database Consultant
Domain Registration and Linux/Windows Web Hosting Reseller
http://www.spacelots.com

[markjerz]

Thanks for your help Erland.

Sadly, I am adding features to existing code so I can't really go
changing too much stuff, will probably just add the NULL value to the
rule.

Michael, according to Microsoft, if you allow nulls in a table column
and then set a rule (like mine) the table structure is supposed to
overwrite the rule to allow nulls. This works when doing simple inserts
but sadly seems to fall down when using INSERT INTO ..... SELECT * FROM
.....

Again, thanks for clearing that up Erland.
Michael Austin wrote:

ma******@googlemail.com wrote:

Hi,

I basically have two tables with the same structure. One is an archive
of the other (backup). I want to essentially insert the data in to the
other.

I use:

INSERT INTO table ( column, column .... )
SELECT * FROM table2

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?

Thanks.

my guess is by using the rule (IN ('Y','N')) - and that because you have said
ONLY Y,N values are allowed. It is essentially a check constraint in other
databases. And I would consider any other behavior a bug.

--
Michael Austin
Database Consultant
Domain Registration and Linux/Windows Web Hosting Reseller
http://www.spacelots.com

[markjerz]

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:

(ma******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[othellomy]

just drop table and recreate it with new rules and then import.

ma******@googlemail.com wrote:

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:

(ma******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[markjerz]

That's not even slightly viable. I have well over a hundred tables
using that rule with a lot of data in them. I've read something about
updating syscomments table??? Not sure of the exact procedure though so
any help would be good.

Thanks.
[e-mail] wrote:

just drop table and recreate it with new rules and then import.

ma******@googlemail.com wrote:

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:

(ma******@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, es****@sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[David Portas]

ma******@googlemail.com wrote:

That's not even slightly viable. I have well over a hundred tables
using that rule with a lot of data in them.

Rules are not viable at all in my opinion. This is perhaps one reason
why they are deprecated in favour of constraints:

"CREATE RULE will be removed in a future version of Microsoft SQL
Server. Avoid using CREATE RULE in new development work, and plan to
modify applications that currently use it. We recommend that you use
check constraints instead. Check constraints are created by using the
CHECK keyword of CREATE TABLE or ALTER TABLE. For more information, see
CHECK Constraints."

I've read something about
updating syscomments table??? Not sure of the exact procedure though so
any help would be good.

There is no exact procedure. Updating system tables is dangerous,
unreliable and unsupported. Don't go there.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[othellomy]

If you have 100s of tables with a faulty rule then you should drop the
whole database and recreate them with a correct rule. It should not
take more than a day to recreate 100s of tables correctly

ma******@googlemail.com wrote:

That's not even slightly viable. I have well over a hundred tables
using that rule with a lot of data in them. I've read something about
updating syscomments table??? Not sure of the exact procedure though so
any help would be good.

Thanks.
[e-mail] wrote:

just drop table and recreate it with new rules and then import.

ma******@googlemail.com wrote:

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:
>
(ma******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx