473,698 Members | 2,203 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Inserting nulls in to table conflicting with rule

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.

Jan 15 '07 #1
8 6520
(ma******@googl email.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****@sommarsk og.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
Jan 15 '07 #2
ma******@google mail.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
Jan 16 '07 #3
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******@google mail.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
Jan 16 '07 #4
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******@googl email.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****@sommarsk og.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
Jan 16 '07 #5
just drop table and recreate it with new rules and then import.

ma******@google mail.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******@googl email.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****@sommarsk og.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
Jan 16 '07 #6
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.
othell...@yahoo .com wrote:
just drop table and recreate it with new rules and then import.

ma******@google mail.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******@googl email.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****@sommarsk og.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
Jan 16 '07 #7
ma******@google mail.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
--

Jan 17 '07 #8
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******@google mail.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.
othell...@yahoo .com wrote:
just drop table and recreate it with new rules and then import.

ma******@google mail.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******@googl email.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****@sommarsk og.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
Jan 17 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
9580
by: Bob C. | last post by:
I want to create a 1-many relationship. Parent table has a primary key, child table has no primary key. The child table does have an index with all four fields of the parent's PK. How can I do this? Thanks, Bob C.
1
5144
by: Michael Albanese | last post by:
I am building an application to report on-the-job injuries and incidents. There are a lot of Date fields, some of which are optional and can be left blank by the user. I have allowed Nulls on these fields in my SQL Server DB, as well as in my stored proc. The problem is that when I submit this data i get an error that states "Input not in date format" or a similar statement when there arevnull date fields.
16
1855
by: bill | last post by:
How can I insert a record with a null value in one of the fields? I need to use the update method from a data adapter. The data is in a XML file.
4
25350
by: epaetz | last post by:
I'm doing a bcp out of a table to a file. Some of the fields in a record may have an empty string. When I bcp out to the file and examine it, the fields that have an empty string in the database now show up in the file as having one blank character. Why is bcp doing this? I don't want the blank character in my output. Thanks,
0
1820
by: LesM | last post by:
This is a change of behaviour between Access 2000 SP3 and Access 2002 SP3. I have Progress table that is linked via ODBC into Access using OpenLink Lite for Progress 9.0b. For over a year, using Access 2000 under Win98, I have been running a Make Table or Append query against this ODBC table and producing an extracted Access table. Any fields that are NULL on the source ODBC table show as NULL on the output table. The query also has an...
13
3044
by: imnewtoaccess | last post by:
Hi, I am getting errors while inserting records in one table from another. These are the structures of two tables : file51tm_new RecordType Text
2
1708
ak1dnar
by: ak1dnar | last post by:
Hi, I am getting some sql errors when trying to insert a record to mysql table using asp.net application. Let me explain the scenario for your reference. This asp.net web form is executing perfectly when i am trying it in my localhost. The error is getting when I tried it with my hosting server. System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Dr_no', table 'myDB.username.tablename'; column does not...
16
18388
priyan
by: priyan | last post by:
Hi all, I had created a view from a table employee from my database. View is created sucuessfully and when i inserted data into employee table it is also updated in the view but i tried to insert data into my view but it shows an error. create view empp as select * from employee Name DOB id --------- ----------- ------ priya 2007-08-20 02:21:41 2 chitra ...
12
9126
by: Michael.Z | last post by:
Anyone who can help: Given a Table.h file I am writing a Table.c file. I keep getting the compile error: previous declaration of Table was here / conflicting types for I think the problem was the result of two pieces of code. First: typedef struct Table; /* in Table.c*/
0
8672
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9156
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9021
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8892
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7712
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5860
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4361
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4614
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
1998
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.