473,722 Members | 2,397 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 6524
(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
9581
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
5145
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
1858
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
25363
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
1824
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
3048
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
18400
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
9131
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
8739
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9238
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...
0
9088
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8052
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...
1
6681
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5995
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
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2602
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.