February 24, 2005 at 2:27 pm
Does anyone know why sql server doesn't seem to enforce rules on computed columns?
For example, if I create the following objects:
create rule test_rule as @value = 0
create table test_table(col1 int, col2 int, col3 as (col1 + col2))
Bind the rule to the calculated column:
exec sp_bindrule 'test_rule', 'test_table.col3'
And insert a row that will break the rule:
insert test_table (col1, col2) values (1, 1)
The row is inserted resulting in:
col1 col2 col3
----------- ----------- -----------
1 1 2
If I bind the same rule to one of the other columns and try the insert:
exec sp_bindrule 'test_rule', 'test_table.col1'
insert test_table (col1, col2) values (1, 1)
The result is:
Server: Msg 513, Level 16, State 1, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement.
Has anyone encountered this before? Is this normal behaviour for sql server? Is there a setting somewhere that would fix this?
Any information would be much appreciated.
February 24, 2005 at 4:58 pm
This actually appears to be a bug in sp_bindrule !!
If you attempt to add a constraint on the table using
alter table test_table add constraint test_table_COL3 check(col3 <> 0 )
Then you will get these messages.
Server: Msg 1759, Level 16, State 1, Line 1
Invalid column 'col3' is specified in a constraint or computed-column definition.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Instead, you need to create the constraint and duplicate the computed column algorithm.
alter table test_table add constraint test_table_COL3 check(col1 + col2 <> 0 )
Here is complete set of SQL statements to illustrate what is possible:
create table test_table (col1 int, col2 int, col3 as (col1 + col2))
go
create rule test_rule as @value = 0
go
-- Bind the rule to the calculated column:
exec sp_bindrule 'test_rule', 'test_table.col3'
go
sp_help test_table
go
insert test_table (col1, col2) values (1, 1)
go
update test_table set col1 = 0, col2 = 0
go
drop table test_table
drop rule test_rule
go
create table test_table (col1 int, col2 int, col3 as (col1 + col2))
go
alter table test_table add constraint test_table_COL3 check(col3 <> 0 )
go
alter table test_table add constraint test_table_COL3 check(col1 + col2 <> 0 )
go
insert test_table (col1, col2) values (1, 1)
go
update test_table set col1 = 0, col2 = 0
go
drop table test_table
go
SQL = Scarcely Qualifies as a Language
February 24, 2005 at 5:27 pm
Rule acts on the event of insert and update while computed column can not be targeted in either.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply