Microsoft KB Archive/280404

{|
 * width="100%"|

PRB: Unable to Update Partitioned Views After Altering Table Constraints

 * }

Q280404

-

The information in this article applies to:


 * Microsoft SQL Server 2000

-

SYMPTOMS
After altering a constraint on a column that participates in a partitioned view, any attempt to update or delete the values in that column through the partitioned view will fail with the following error message:

Server: Msg 4436, Level 16, State 12, Line 1

UNION ALL view 'v1' is not updateable because a partitioning column was not found.

CAUSE
Disabling a check constraint marks the constraint as untrusted, because the user can perform inserts or any other operation that violates the check constraint, without the server performing consistency checks. Later, when this check constraint is re-enabled, it still remains marked as untrusted because there is no way to validate the data for that column. The end result is that the query optimizer cannot use such constraints to filter out unwanted branches in the partitioned view.

WORKAROUND
To work around this problem, drop and re-create the constraint.

MORE INFORMATION
This behavior is by design. The following example explains the scenario for this problem.

On Computer1

use pubs go drop table t1 go create table t1 (c_int int not null) go create unique clustered index i1 on t1(c_int asc) go alter table t1 add constraint c1 primary key (c_int) alter table t1 add constraint c2 check (c_int >=1000) go On Computer2

use pubs go drop table t1 go create table t1 (c_int int not null) go create unique clustered index i1 on t1(c_int desc) go alter table t1 add constraint c1 primary key (c_int) go alter table t1 add constraint c2 check (c_int < 1000) go On Computer1

-- Create the view definition. use pubs go drop view v1 go create view v1 as  select * from rem1.coyote.dbo.t1   union all select * from coyote.dbo.t1 go set xact_abort on      go  insert into v1 (c_int) select 1 -- This works. insert into v1 (c_int) select 1100 go update v1 set c_int = c_int -- This works. go alter table t1 nocheck constraint c2 -- Disable the check constraint. go alter table t1 check constraint c2 -- Enable the check constraint. go update v1 set c_int = c_int -- Stops working. go delete from v1 -- Stops working. go Additional query words:

Keywords :

Issue type : kbprb

Technology :