Posted by markjerz on 01/16/07 09:36

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:

> markjerz@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

[Back to original message]